Tag - SQL Server Errors

SQL Server Error database principal owns schema cannot be dropped

Recently i was working on one of my SQL Server database project and i got this error message

"The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)"

I spent good couple of hours to dig the problem and finally find the solution. 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.

Lets suppose we have a database 'SharePoint2010' and we are trying to delete user 'uahmed'

Run following query against SharePoint2010 database

USE SharePoint2010;

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('uahmed');

As a result i am getting these two schema

SQL Server Error database principal owns schema cannot be dropped

Now we need to alter authorization of this schema to other schema so that we could drop database owner. In our scenario we have to run alter authorization query twice.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;

ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you will try to drop database owner it will not throw error message.

I hope this post will help you to solve this error message quickly without wasting any time. Please post your comments thanks

Read more...