Last day I had excellent fun asking puzzle on SQL Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every single time he was getting error and was not able to remove the user.
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
As per him it was very urgent and he was not able to solve the same. I totally understand his situation and here is the quick workaround to the issue. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.
Workaround / Resolution / Fix:
Let us assume that user was trying to delete user which is named as ‘pinaladmin’ and it exists in the database ‘AdventureWorks’.
Now run following script with the context of the database where user belongs.
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin');
In my query I get following two schema as a result.
Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
Now if you drop the database owner it will not throw any error.
Here is generic script for resolving the error:
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('YourUserID');
Now replace the result name in following script:
ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;
Reference: Pinal Dave (https://blog.sqlauthority.com)
129 Comments. Leave new
Try this sp_changedbowner ‘sa’
life saver!!!!
Thanks a lot, this saved me on my production server.
Thanks! This is a nicely documented explanation and solution.
Thanks! This is a nicely documented explanation and solution – as usual for Pinal.
Excellent!!!
Worked Perfectly!! Thanks!
Thank you, this worked perfectly for me :)
Thank you !! worked fine
Hello, I changed the scheme but i still get the catalog error.
TITLE: Microsoft SQL Server Management Studio
Drop failed for User ‘eCoNoDatUser’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The database principal owns a fulltext catalog in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
This blog is always very very helpful
Thank you, worked fine, this blog is very helpful
Thank you, a useful article.
Wow it’s very cool..my problem solved in instance
thanks alot, it helped me alot.
Thanks dude…
Thanks!
Did not work for me … The schema query for the user_id returned no results. The database user (login name) is mapped to the dbo user but it only has a SQL Login. The SQL Login name is mapped to the database as User ‘dbo’, Default Shema ‘dbo’, and has the db_owner role on the database.
Disregard previous post: the issue was resolved with an ‘alter authorization on database::databaseName to NewLogin’..
Thank you very much for your help.
Your welcome @Steve
merci beaucoup cela marche parfaitement bizn