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

30 June,2016 by Jack Vamvas

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 ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer