Set owner failed for database an exception occurred in smo - Msg 15110, Level 16, State 1, Line 1

Question : I’m trying to change the database owner on a user database to  sa, but I am receiving an error message:

ALTER AUTHORIZATION ON DATABASE::myDatabase TO [sa]
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database

How can I fix this error?

Answer: It is possible that the login used in the RESTORE of the database is not equal to the owner which was previously the owner of the database.   The database owner is defined as the sys.server_principals login owning the database.

The first thing to check is if the database owner_sid is equal or not equal to the databases’s db_owner_sid.

The two queries to use for this procedure are

use master
go
select SUSER_SNAME(owner_sid) from sys.databases where name = 'myDatabase'
select SUSER_SNAME(sid) from RMS_DB_TOOLS.sys.database_principals where name = 'dbo'

The main clue is the error message , indicating the proposed database owner is already mapped as a user on the database.

So the fix is to DROP the ‘sa’ user from the user database. Then run the ALTER AUTHORIZATION statement to associate the proposed login to become the database owner.

If the user owns a schema   read Msg 15138 - The database principal owns a schema in the database and cannot be dropped.

USE myDatabase;
DROP USER sa;
ALTER AUTHORIZATION ON DATABASE::myDatabase TO [sa];

Read More

ALTER AUTHORIZATION sql to change owner of a database (SQL ...

Script to change database owner to sa for all user databases (SQL ...