Removing a user from SQL Server database who owns a schema


Updated: 21 October 2020

Your customer urge you to drop a user from the MsSQL 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.

That means that the user owns one or more shcemas of your database. 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 Objeto, su.name Owner
from sys.schemas so
inner join sysusers su on so.principal_id = su.uid
where su.name = 'username'

select so.name Objeto, su.name Owner, so.xtype Tipo
from sys.sysobjects so
inner join sysusers su on so.uid = su.uid
where su.name = 'username'

Once you have the objects/schemas owned by the user, you can change them with the following SQL (schema change sample):

USE [DATABASENAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dbo] -- new owner username
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dbo]
GO

Then you're ready to drop the user DROP USER [username]

How to list all the schemas and it's owners?

Here's a script to list schemas and it's owners.

USE [DATABASENAME]
GO
SELECT
    schema_name(schema_id) AS schemaname,
    user_name(schm.principal_id) AS username, *
FROM sys.schemas AS schm
GO

Translations: