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
Biography
Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
Thank you. This was very helpful