By: Jugal Shah | Comments (15) | Related: > Security
Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.
Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropped. Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped.
Solution
In this article I will explain what needs to be done prior to dropping the user if it failed with error message 15421 or error message 15138.
As per Microsoft SQL Security you cannot drop a user in one of the below scenarios:
- Database Principal/User owns a database role
- Database Principal/User owns a schema
To learn more about orphaned users take a look at this tip Understanding and dealing with orphaned users in a SQL Server database.
Query to Get Database Roles Owned by a User
You can run this script to get a list of database roles owned by a particular user. Just substitute the orphaned user name where I have "Jugal".
-- Query to get the user associated Database Role select DBPrincipal_2.name as role, DBPrincipal_1.name as owner from sys.database_principals as DBPrincipal_1 inner join sys.database_principals as DBPrincipal_2 on DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id where DBPrincipal_1.name = 'Jugal'
Query to Get Database Schemas Owned by a User
You can run this script to get a list of schemas owned by a particular user. Just substitute the orphaned user name where I have "Dj".
-- Query to get the user associated schema select * from information_schema.schemata where schema_owner = 'Dj'
As a next step to fix the issue we will transfer the ownership of the database role or schema to DBO. We can do this using either SSMS or a T-SQL script.
Fix Error Msg 15421
Using SSMS to Fix the Error
Go to Object Explorer > Connect to the Target Server > Expand the target Database > Expand Security > Expand Roles > Expand Database Roles-> Right Click on the database role that you need to modify. You can see the user name "Jugal" as the owner. Change it to "dbo" or some other user to resolve the error. You can just enter the user and click OK to save the change or click on the "..." to find a user.
Using a Script to Fix the Error
Here we are transferring ownership of the "db_owner" role to "dbo".
--Query to fix the error Msg 15138 USE [db1] GO ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo] GO
Fix Error Msg 15138
Using SSMS to Fix the Error
Go to Object Explorer > Connect to the Target Server > Expand the target Database > Expand Security > Expand Schemas > Right Click on the schema that you need to modify. You can see the user name "Dj" as the owner. Change it to "dbo" or some other user to resolve the error. You can just enter the user and click OK to save the change or use Search... to find a user.
Script to Change the Authorization
Here we are transferring ownership of schema "djs" to "dbo".
--Query to fix the error Msg 15138 USE [db1] GO ALTER AUTHORIZATION ON SCHEMA::[djs] TO [dbo] GO
Dropping the User
Now that the schema and/or database role has been transferred to "dbo" you should be able to drop the user.
Next Steps
- Learn more about orphaned users
- Add the above validation to your Orphan User fix script
About the author
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
