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

Problem:

When you try to delete user from database through Microsoft SQL Server Management Studio you might stumble in the follow error:

“Drop failed for User ‘user’. (Microsoft.SqlServer.Smo).”
“The database principal owns a schema in the database, and cannott be dropped”

userCannotDrop

In simple terms, the user you want to delete has the property of one or more schema.

Solution:

Applied to: Microsoft SQL Server 2008 R2 (but it’s similar for the other).

We have to find the schema of where the user is the owner. So execute new query on the database where you want to delete user:

SELECT sc.name
FROM sys.schemas sc
WHERE sc.principal_id = USER_ID('my_user')

Once you find the schema to which it belongs (eg db_owner) you have to transfer it to another user or dbo.

You can do it through  Microsoft SQL Server Management Studio or execute new query on the database (as you did before):

ALTER AUTHORIZATION
ON SCHEMA::db_owner
TO dbo

Best Regards,

ITquadro – specialized Blog

ITquadro consulenti informatici specializzati in ambienti microsoft sql server. Ci trovate su Bologna ed Emilia-Romagna ma anche su Milano e Roma.