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”
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.