Category: Windows

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