Fixing error: The database principal owns a schema in the database, andcannot be dropped

A user can be dropped using DROP USER command. But if that user owns any object that we get an error “The database principal owns a schema in the database, and cannot be dropped.”

To resolve this issue first we will have to know that what objects are owned by that particular user. Below is the query to retrieve the list of all objects that are owned by a user (here in script “UserA”).

SELECT o.name ObjectName, o.type_desc as ObjectType

FROM sys.objects o INNER JOIN sys.database_principals p

ON p.principal_id = o.principal_id

WHERE p.name = 'UserA'

UNION

SELECT s.name, 'Schema'

FROM sys.schemas s INNER JOIN sys.database_principals p

ON p.principal_id = s.principal_id

WHERE p.name = 'UserA'

This script returns a result as in following screenshot:



Here we got the list of objects that are owned by user UserA.

Now we will alter the owner of all these objects to different owner using the ALTER AUTHORIZATION command. After that the DROP USER command would complete successfully.