About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
DROP USER fails with error 15138 when the database user you are removing still owns something inside that database, such as a schema. Microsoft's DROP USER page says users that own securables cannot be dropped, so you must drop the item or transfer its ownership first. Find everything the user owns, transfer each item to a principal that should keep it, then run DROP USER again.
What the error means
Microsoft's error list gives error 15138 as severity 16, with the description "The database principal owns a %S_MSG in the database, and cannot be dropped." The placeholder is filled in at run time. A Microsoft Dynamics GP support page shows the filled-in form: "The database principal owns a schema in the database, and cannot be dropped."
A schema is a container for objects such as tables, views and procedures, and every schema has an owner. In sys.schemas the owner is the principal_id column. Microsoft notes that schemas are not equivalent to database users. A user and a schema can share a name and still be separate things, and DROP USER removes only the user.
The same family of errors blocks DROP USER for other owned items. Microsoft's page on upgrade failures lists, among others:
- 15183: the principal owns objects in the database.
- 15184: the principal owns data types in the database.
- 15421: the principal owns a database role.
- 15136: the principal is the execution context of procedures, functions or event notifications.
- 15284: the principal has granted or denied permissions to objects.
Fixing 15138 can therefore reveal a second error with a different number. Work through the list until DROP USER succeeds.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| The user owns one or more schemas | Error 15138 | sys.schemas rows whose principal_id matches the user |
| The user owns a database role | Error 15421 | sys.database_principals rows of type R whose owning_principal_id matches the user |
| The user owns individual objects inside someone else's schema | Error 15183 | sys.objects rows whose principal_id matches the user |
| The user owns types or XML schema collections | Error 15184 | The owner query for types and XML schema collections below, run for each schema the user owns or has objects in |
| The user is set as the execution context of a procedure, function or event notification | Error 15136 | The error text itself: "set as the execution context of one or more procedures, functions, or event notifications" |
Prove the cause before changing anything
All checks below only read metadata. Run them in the database where DROP USER failed. Microsoft documents that catalog views show only securables you own or hold a permission on, so use a login that is a member of sysadmin or has db_owner in that database. Otherwise the result can look empty when it is not.
Schemas owned by the user:
SELECT s.name AS schema_name, dp.name AS owner_name
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON dp.principal_id = s.principal_id
WHERE dp.name = N'CONTOSO\jsmith';
Roles owned by the user:
SELECT r.name AS role_name
FROM sys.database_principals AS r
JOIN sys.database_principals AS o ON o.principal_id = r.owning_principal_id
WHERE r.type = 'R' AND o.name = N'CONTOSO\jsmith';
Objects the user owns that differ from the schema owner. Microsoft describes sys.objects.principal_id as the ID of the individual owner, if different from the schema owner, and NULL when there is no alternate owner:
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name, o.type_desc
FROM sys.objects AS o
JOIN sys.database_principals AS dp ON dp.principal_id = o.principal_id
WHERE dp.name = N'CONTOSO\jsmith';
For data types and XML schema collections, Microsoft's sys.objects page (example D, "Return the owner of each object in a schema") returns the owner of each object, type and XML schema collection in a named schema. Replace <schema_name> with each schema in turn:
SELECT 'OBJECT' AS entity_type,
USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name,
name
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'TYPE' AS entity_type,
USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name,
name
FROM sys.types
WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type,
COALESCE(USER_NAME(xsc.principal_id), USER_NAME(s.principal_id)) AS owner_name,
xsc.name
FROM sys.xml_schema_collections AS xsc
INNER JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
Microsoft's ALTER AUTHORIZATION page adds a warning: "The only reliable way to find the owner of an object is to query the sys.objects catalog view. The only reliable way to find the owner of a type is to use the TYPEPROPERTY function."
A good result is that every query returns no rows for the user. A bad result is one or more rows: each row is something that blocks DROP USER.
Before you move anything, save the permissions that sit on what you will transfer, with the securable each one is on. These queries are built from Microsoft's sys.database_permissions examples, which join to sys.objects and sys.schemas for object names; class 1 is an object or column, 3 is a schema and 4 is a database principal such as a role. Replace reports and report_readers with your names.
Permissions on objects (and columns, shown by minor_id) in the schema:
SELECT pr.name AS grantee, pe.state_desc, pe.permission_name,
s.name + N'.' + o.name AS securable, pe.minor_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE pe.class = 1 AND s.name = N'reports';
Permissions granted on the schema itself:
SELECT pr.name AS grantee, pe.state_desc, pe.permission_name, s.name AS schema_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.schemas AS s ON pe.major_id = s.schema_id
WHERE pe.class = 3 AND s.name = N'reports';
Permissions granted on a role as the target:
SELECT pr.name AS grantee, pe.state_desc, pe.permission_name, tp.name AS role_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.database_principals AS tp ON pe.major_id = tp.principal_id
WHERE pe.class = 4 AND tp.name = N'report_readers';
Microsoft notes that permissions of fixed database roles do not appear in sys.database_permissions, so a principal may hold more than the output shows. Keep the output with your change record.
Fix
Decide who should own each item. For a schema, that is a principal that will exist after the user is gone and that suits the application, for example dbo or a role. Microsoft's remarks say ownership of database-contained entities can be transferred to any database-level principal.
Disruptive step: Transfer a schema. Microsoft documents that when schema ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped.
ALTER AUTHORIZATION ON SCHEMA::[reports] TO [dbo];
Back-out: run the same statement with the original owner, but dropped permissions do not come back by themselves. Re-apply them from the saved output. Microsoft also states that you cannot change the owner of sys, dbo or INFORMATION_SCHEMA.
Disruptive step: Transfer a role.
ALTER AUTHORIZATION ON ROLE::[report_readers] TO [dbo];
Back-out: run it again with the previous owner. The permissions section of the ALTER AUTHORIZATION page says: "If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped." For a role as the target, that means permissions granted on the role itself (the third query above), not the permissions its members hold through it. Treat roles and objects the same way as schemas and re-apply the saved grants.
Disruptive step: Transfer an individual object to the owner of its schema. This example form is on Microsoft's page.
ALTER AUTHORIZATION ON OBJECT::reports.Sales2026 TO SCHEMA OWNER;
Back-out: as above. For the OBJECT class Microsoft says: "Cannot change ownership of triggers, constraints, rules, defaults, statistics, system objects, queues, indexed views, or tables with indexed views." A request to move one of them fails. Its page also lists linked servers and credentials among the non-transferable items.
Microsoft's permissions note for ALTER AUTHORIZATION: the person running these statements needs TAKE OWNERSHIP on the entity. If the new owner is a different person, they also need IMPERSONATE on a user or login new owner, or, for a role, membership of the role or ALTER on the role.
Disruptive step: Drop the user. Microsoft says this needs ALTER ANY USER on the database.
DROP USER [CONTOSO\jsmith];
There is no way back inside SQL Server except to create the user again and re-grant its permissions. Keep the saved permission output and the login mapping.
A separate case: if error 15138 appears in the SQL Server error log while a cumulative update or service pack fails to finish, with errors 912 and 3417, Microsoft's page ties it to certificate-based server principals whose names sit inside double hash marks. Those principals own user objects in msdb or other databases. Microsoft's documented route starts SQL Server with trace flag 902, lists the mappings with EXEC master.sys.sp_helplogins, changes the ownership of the affected objects to a different user, and restarts without the flag. Risk: that route involves restarting the instance and running it with a trace flag, so treat it as an outage task and do not attempt it on a running production instance without a window. The way back is to restart without the flag.
Confirm the fix
Re-run the schema, role and object queries. Each should return no rows for the user. Then run DROP USER and confirm the principal is gone from sys.database_principals. Finally, run the application's normal tasks as its own account and compare the permission list with the one you saved, so a lost grant shows up now rather than in the next report run.
Example (illustrative): on sql01, database SalesDb, the departing user CONTOSO\jsmith owns the schema reports, which holds 14 tables and 6 views, so 20 objects. The three permission queries, run before the change, saved 9 rows for that schema and its objects. After ALTER AUTHORIZATION to dbo, the same queries return no rows, so the administrator applies all 9 grants again from the saved list. DROP USER then succeeds with no further error, and the three report accounts still read the 20 objects.
What to watch afterwards
- Add this check to your leaver process: run the owner queries before disabling a login, not after.
- Prefer schemas and roles owned by dbo or a role rather than by a named person. A named owner turns every departure into this error.
- Watch for orphaned grants after any ownership change, particularly on schemas used by reports.
- Note that principal IDs can be reused after a drop, as Microsoft states, so never store them as a long-term reference.
Read next
Related articles you may want next.
- TroubleshootingThe Database Restored, but the Application Cannot Log InTrace SQL logins, database users and SID mappings after a restore, and validate application access without broadening application privileges.Read article
- HostingRevoke a Departed Administrator's Access Across a Hosted EstateAdministrator left? Inventory every route first, then disable AD and Entra accounts, revoke sessions, remove vCenter and ESXi permissions, SSH keys and shared secrets, with evidence.Read article
- Backup & RecoverySQL Agent backup job fails with error 15404: could not obtain information about Windows userSQL Agent job fails with error 15404 'Could not obtain information about Windows NT group/user'? Check the job owner, test it, then change it deliberately.Read article
Risks and limits
These commands have not been run or lab-tested. Behaviour is described from the Microsoft pages listed below, and the exact error text depends on the item type, since the message placeholder is replaced at run time. Ownership transfers can silently remove permissions, so the saved list is the only safeguard. Dropping a user does not drop the server login, which is a separate step with its own risks. Azure SQL and other platforms listed on the Microsoft pages may differ in details not covered here.
Sources
- Database Engine events and errors (15000 to 15999) — Microsoft Learn, page dated 2026-10-27, accessed 18 November 2026
- DROP USER (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- ALTER AUTHORIZATION (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- sys.schemas (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- sys.database_principals (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- sys.objects (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- sys.database_permissions (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- Drop failed for User user name (Dynamics GP) — Microsoft Learn, page dated 2025-04-17, accessed 18 November 2026
- SQL Server upgrade fails if certificate-based principals own user objects — Microsoft Learn, page dated 2023-08-17, accessed 18 November 2026
This article was originally published on .