Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

By: Jugal Shah   |   Updated: 2012-02-29   |   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
  • Understanding and dealing with orphaned users in a SQL Server database
  • Script to Find and Drop All Orphaned Users in All Databases
  • Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins
• 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