Tags

, , , , ,


Today one of my reader asked a question about how to fix the error while trying to remove a login from a database. The reader was getting the below error:

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and 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:

Image

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.

Cheers,

Subhro Saha

Advertisements