Sql-server – Error on changing ownership of database from Files Page in database properties dialog box

sql serversql-server-2008-r2

I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error

Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects

Now when I tried to change ownership from Files page of Database Properties I am getting error as

Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO)
Additional Info: an exception occurred in SMO
The proposed new database owner James-PC\James is mapped as user James in this database.
Parameter name: James-PC\James"

Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.

Best Answer

The database owner is the login in sys.server_principals that owns the database, as defined by its SID. After a restore it may easily be the case that the login that was used to restore the database is not the login that was the previous owner of the database. (This is even more likely to happen when the database if moved between servers.)

So, there could be three settings that you are having trouble with

  1. The database owner_sid is not equal to the database's db_owner sid. You can compare this for database ABC by:

    SELECT owner_sid FROM sys.databases where name = 'ABC';

    SELECT sid from ABC.sys.database_principals WHERE name = 'dbo';

  2. Next you are getting a message saying that your proposed database owner_sid also exists as a user in sys.database_principals. We know this from the error message about mapping the proposed owner.

So your steps are:

 USE ABC;
 DROP USER James;
 ALTER AUTHORIZATION ON DATABASE::ABC TO [James-PC\James];

You dropped the user which is not needed, since you are making it the dbo. Once that is out of the way, then your login can be made the owner of the database.

EDIT: You can also use SSMS to delete the James user from ABC database. Then you could return to the Database properties File tab to set the database owner. (Sorry, I tend to think scripts.)

Related Question