The Database Principal Owns a Schema in the Database, and Cannot be Dropped

I encountered a problem today and thought to share with all of you so that in the future, if you confront such an issue then this article may help you.

So let's dig into Problem.

I was trying to remove the login from the database, and wanted to drop a user but it was preventing me from doing that. What was raising an issue at each attempt?

The following window prompts at each attempt, kindly have a look:



It simply states "The database principal owns a schema in the database, and cannot be dropped".

To sort it out I had to spend time and finally found a resolution for this.

Let us move ahead and look into the workaround of this issue.

A user, say "sa", was trying to delete a user named "sachinkalia" and it exists in the database "Northwind".

Now run the following script with the context of the database where the user belongs.

USE Northwind;
SELECT s.name
FROM sys.schemas s

WHERE s.principal_id = USER_ID('sachinkalia');



I ran this query on my production systemn so due to some confidentially I made the server name and user name red. Kindly look at the result, it shows the name as db_owner and db_datareader.

This is the username that exists in my database, which is marked with red:


Run the following query to change it:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;

After successful completion of this query it will allow dropping the user and will help you to get rid of this issue.

drop user sachinkalia

After dropping, the database structure will look like this:

I hope this will help you someday.

Cheers .Net