[MSSQL 2012] Drop User Who Owns a Schema

While dropping a user, you may get an error stating:

Drop failed for User '<user_name>'.  (Microsoft.SqlServer.Smo)

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

The error appears when the user owns a schema in database. The drop such user, you're required to check the schema's that this user own.You can do this by using following query:

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('<user_name>');

Note: <user_name> is your actual user name whom you're trying to drop.

This will return the list of schema that user own. 

Taking each schema one by one, you can change the ownership to another user using following query:

ALTER AUTHORIZATION ON SCHEMA::<schema_name> TO <user2_name>;

Note: <schema_name> is the name of schema returned from previous query and <user2_name> is another existing user, which will own that schema after this change.

After changing the ownership for all the schema returned from first query, if you re-run the same you'll get no result. After this you can easily drop the user.