Windows LiveWindows Live™
 
 
個人檔案Nates Stuff相片部落格清單更多 工具 說明

部落格
    10月29日

    Restoring a SQL Server 2005 Database On A Different Machine

    If you do a lot of programming on a SQL 2005 database, chances are this has happened to you!  You restore a database and try to diagram it (or make some other change) and get this error:
    SQLError
    TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------
    Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
    ------------------------------
    BUTTONS:
    OK
    ------------------------------
    What a pain!  Here is how you fix it!
    • Open up a new query window
    • Execute the command:
      • ALTER AUTHORIZATION ON Database::[MY_DATABASE_NAME] TO [MY_USER_NAME]
        • Replace [MY_DATABASE_NAME] with the name of your database
        • Replace [MY_USER_NAME] with a valid user in your system but one that does not yet exist in the restored database
        • Example:  ALTER AUTHORIZATION ON Database::CorporateDB TO sa

    I hope this helps someone!  It's not as easy to change as I think it should be!

    Nathan Zaugg

     
    UPDATE:
     
    Another possible fix is found if you are geting the error "The proposed new database owner is mapped as a user in this database"
     
    USE MyDatabase
    EXEC sp_addlogin     'TempOwner'
    EXEC sp_changedbowner     'TempOwner'
    EXEC sp_changedbowner     'sa'
    EXEC sp_droplogin 'TempOwner'

    下午 04:23 | 新增回應 | 永久連結 | 發佈至您的部落格 | Database