SQL Server Msg 15138 - The database principal owns a schema in the database, and schema cannot be dropped

The error message of SQL Server is self explanatory as there were schema associated with the user and we have to transfer those schema before removing the User.
In this post I will explain the workaround for this error:

Lets assume I am trying to drop a user named “TestUser” from DemoDB database.
Now, run the below query in the database from which we are trying to drop the user.

Use DemoDB ;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘TestUser’)

In my test scenario, I got the below result set from the above query:

Then, use the names found from the above query below in place of the SchemaName below. And drop your user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
GO
DROP USER TestUser

In my Test scenario I used the below queries:

ALTER AUTHORIZATION ON SCHEMA::db_securityadmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;

GO

DROP USER TestUser

Hope this helps.

Original Source

Related Post :