BI Blog,  SQL Server

SQL Server drop user failed: “The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)”

If you try to delete a user who owns a schema, you will receive the following error:

Drop failed for User ‘my_user’.  (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Therefore, to delete the user, you have to find the schema to which it owns, and transfer the ownership to another user (or role).

Use this query to find the schema the user belongs:

 

SELECT sc.name

FROM sys.schemas sc

WHERE sc.principal_id = USER_ID('my_user')

Once you find the schema with the previous query (eg “db_datareader”), use it to transfer the ownership with this snippet:

 

ALTER AUTHORIZATION

ON SCHEMA::db_datareader

TO dbo




Did you like this article? Help me keep the blog alive with a little donation!






Leave a Reply

Your email address will not be published. Required fields are marked *