Restoring a SQL Server 2005 Database On A Different Machine
Posted
Thursday, January 10, 2008 2:47 PM
by
Nathan Zaugg
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:
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 getting 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'