My Favorite Blogs

Thursday, January 5, 2012

Msg 15110, Level 16, State 1, Line 1 The proposed new database owner is already a user or aliased in the database

              Today I got an user request to change  database owner for couple of databases on a lab environment. The user tried to change the owner by himself  but he got "Msg 15110, Level 16, State 1, Line 1" error, because the user name is already exists in the database which is erroring out. I looked into that and tried to change the db owner and got the same error as below.


-- Step 1
sp_changedbowner 'SQL\DBOPS'
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

 Based on the above error I decided to  drop the user from the db but I got different error now as below.

drop user [SQL\DBOPS]
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

 Based So I checked that the user owns which schema on the db using the below statement and found that the user have a schema with the same name which is blocking to drop the user.

-- Step 2
select * from information_schema.SCHEMATA
where schema_owner ='SQL\DBOPS'

CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
DEFAULT_CHARACTER_SET_CATALOG
DEFAULT_CHARACTER_SET_SCHEMA
DEFAULT_CHARACTER_SET_NAME
DBOps_UsageAndHealthDB
SQL\DBOPS
SQL\DBOPS
NULL
NULL
iso_1


In order to drop the schema, first I've to remove/change the schema owner  to something else, so I've to  run the below statement, the below statement change the schema owner to dbo from SQL\DBOPS user.

--Step 3
Alter authorization on SCHEMA:: [SQL\DBOPS]  to dbo
Command(s) completed successfully.

 Run the below SELECT statement again to make sure no other schemas owned by the user.  
--Step 4
select * from information_schema.schemata
where schema_owner ='SQL\DBOPS'
--Result empty

Now I'm good to drop the user and change the owner of the db. 
--Step 5
drop user [SQL\DBOPS]
Command(s) completed successfully.

--Step 6
sp_changedbowner 'SQL\DBOPS'
Command(s) completed successfully.
--succeeded



Issue resolved now... :)

1 comment: