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;

Microsoft’s reference covers DROP USER, which states the rule this error enforces (a user that owns securables cannot be dropped until ownership moves or the securable goes), and ALTER AUTHORIZATION in full.

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,
       dp.type_desc AS owner_type,
       (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  dp.type IN ('S','U','G')                       -- owned by a user or group, not a role
  AND  s.name NOT IN ('dbo','guest','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. The filter on owner type matters: without it the list fills with db_owner, db_datareader and the other built-in schemas, which are owned by the fixed roles, always empty, and not yours to touch. If the object count is 0 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.

Common Questions

Related Scripts

• Orphaned Users (15023), the error you meet if you drop the login before the users
• Get Orphaned Users, the users left behind when a login goes before them, which is the Do This Before You Drop the Login check as a script
• 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