So, have you ever been in the need to drop a user from the SQL Server database, but you stuck with the following related error?
Error: 15138 The database principal owns a schema in the database, and cannot be dropped.
If you don’t have access to the SSMS to see which schema or objects the user owns, the following SQL should do the job.
USE [DATABASENAME] GO SELECT so.name OBJECT, su.name OWNER FROM sys.schemas so INNER JOIN sysusers su ON (so.principal_id = su.uid) WHERE su.name = 'username' GO SELECT so.name OBJECT, su.name OWNER, so.xtype TP FROM sys.sysobjects so INNER JOIN sysusers su ON (so.uid = su.uid) WHERE su.name = 'username' GO
Once you have the owned objects/schemas, you can change them with the following SQL (schema example):
USE [DATABASENAME] GO ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [newowner] /* new owner username */ ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [newowner] GO
Then you’re ready to drop the user DROP USER [username].
Bonus SQL: The following will list the schemas and their owner.
SELECT schema_name(schema_id) as SCHEMANAME, user_name(s.principal_id) as USERNAME FROM sys.schemas AS s