The Database Principal Owns a Schema in the Database and Cannot Be Dropped (Error 15138)

DROP USER refuses because dropping the user would leave a schema with no owner. The fix is one statement, but only after you find out what is owned, because a schema is not the only thing that blocks this and the error only reports the first blocker it hits.

Find Everything That Principal Owns

DECLARE @user sysname = N'AppUser';

SELECT 'schema' AS owned_type, s.name AS owned_name
FROM   sys.schemas AS s
WHERE  s.principal_id = DATABASE_PRINCIPAL_ID(@user)
UNION ALL
SELECT 'role', r.name
FROM   sys.database_principals AS r
WHERE  r.type = 'R'
  AND  r.owning_principal_id = DATABASE_PRINCIPAL_ID(@user)
UNION ALL
SELECT 'object', o.name
FROM   sys.objects AS o
WHERE  o.principal_id = DATABASE_PRINCIPAL_ID(@user);

Three separate ownership classes, three separate errors if you only fix one. A role owned by the user raises 15421 instead of 15138, which sends people looking for a different problem.

The Fix

Reassign the schema, then drop the user:

ALTER AUTHORIZATION ON SCHEMA::[AppUser] TO [dbo];
GO
DROP USER [AppUser];

dbo is the right new owner in almost every case. Assigning it to another real user recreates the same problem the next time that user leaves.

For a role, the statement is the same shape:

ALTER AUTHORIZATION ON ROLE::[AppRole] TO [dbo];

For an individually owned object, the object usually should not have had an owner other than its schema in the first place:

ALTER AUTHORIZATION ON OBJECT::[AppUser].[SomeTable] TO SCHEMA OWNER;

Why the User Owned a Schema Nobody Created

This surprises people, and it has a mundane cause. When a user is created without an explicit DEFAULT_SCHEMA, some tooling creates a schema of the same name and makes the user its owner. So CREATE USER AppUser can quietly leave you with a schema called AppUser, owned by AppUser, containing nothing at all.

SELECT s.name AS schema_name,
       dp.name AS owner_name,
       (SELECT COUNT(*) FROM sys.objects AS o WHERE o.schema_id = s.schema_id) AS object_count
FROM   sys.schemas AS s
JOIN   sys.database_principals AS dp ON dp.principal_id = s.principal_id
WHERE  s.name NOT IN ('sys','INFORMATION_SCHEMA')
ORDER  BY object_count, s.name;

Run that after a migration and you often find a row of empty, orphaned, user-named schemas. If the object count is zero and nothing references it, the schema can be dropped rather than reassigned:

DROP SCHEMA [AppUser];
GO
DROP USER [AppUser];

Check the count first. Dropping a schema that holds objects fails, which is safe, but reassigning a schema that holds the application’s tables to dbo changes nothing about permissions and is the better default when you are unsure.

Do This Before You Drop the Login

Dropping the server login while database users still exist leaves orphaned users behind, which is a different error at a worse time. Drop the database users first, in every database, then the login.

Related Scripts

• Orphaned Users (15023), the error you meet if you drop the login before the users
• Get Permissions and Role Membership, what the principal could actually do before you remove it
• SQL Server Security Scripts, logins, permissions and role auditing across the instance

Common Questions

Related Scripts

• Orphaned Users (15023), the error you meet if you drop the login before the users
• Get Permissions and Role Membership, what the principal could actually do before you remove it
• SQL Server Security Scripts, logins, permissions and role auditing across the instance