Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Jugal Shah By:   |   Read Comments (8)   |   Related Tips: More > 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'

sql query to get a list of database roles owned by a user

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'

sql query to get a list of schemas owned by a user

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.

ssms change database role owner

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.

ssms change schema owner

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


Last Update: 2/29/2012


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 01, 2012 - 1:43:04 AM - Changesh Chaudhari Read The Tip

This artical is very good   but some part get confused . you can wrote  orphaned user name where "Jugal" for data base role own for perticular user and second time  schemas owned by a particular user  orphaned user name where "Dj". 

1)if any know only about data base user name only eg. "jugal" then how he find out schema owned  using data base user name eg"jugal"

.


Thursday, March 01, 2012 - 3:05:51 AM - suman Read The Tip

how to integrate sq server 2005 to 2008? give me step by step preocess?


Thursday, March 01, 2012 - 11:40:35 PM - Jugal Read The Tip

Hi Suman,

Can you please give more details on your question?

Thanks,

Jugal

 

 

 


Wednesday, March 07, 2012 - 8:54:44 AM - Babji talluri Read The Tip

very nice aricle sir!!!!!!!!!!


Friday, March 16, 2012 - 6:29:28 AM - Jon Read The Tip

Good article, only one comment is wrong:

--Query to fix the error Msg 15138    this should be 15421  ;-)
USE [db1]
GO
ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo]
GO

 


Thursday, June 14, 2012 - 6:02:39 AM - Neil Miller Read The Tip

FYI you can shorten your first query a little to:

select

name, * from sys.database_principals where owning_principal_id =USER_ID('jugal')


Tuesday, July 10, 2012 - 3:00:33 AM - VAhid Read The Tip

Hello

I have a database server that users are connected through to it but i dont know a user is that drop database on server

i write a trigger and log history but again user can delete my database on server and trigger cant prevent that it

 

CREATE

Trigger [LogDB] onall

server

For

DROP_database,ALTER_database,ALTER_TABLE,DROP_TABLE,CREATE_DATABASE,Drop_Trigger

as

set

nocount

on

PRINT

'You must contact a DBA before dropping or altering tables!'

rollback

Declare

@CommandText

nvarchar(2000),

@ComputerName

nvarchar(100),

@ApplicationName

nvarchar(100),

@LogiName

nvarchar(100),

@LogDate

DateTime

SELECT

@CommandText =EVENTDATA().value

(

'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'

)

select

@ComputerName

=HostName,

@ApplicationName

=Program_name,

@LogiName

=suser_sname(),

@LogDate

=GetDate()

from

sys.sysprocesseswhere spid=

@@Spid

Insert

LogError.dbo.LogEvents

(

CommandText

,

ComputerName

,

ApplicationName

,

LogiName

,

LogDate

)

values

(

@CommandText

,

@ComputerName

,

@ApplicationName

,

@LogiName

,

@LogDate

)


Monday, December 03, 2012 - 12:27:30 PM - Wanda Read The Tip

this was great! It allowed me to remove the user!  Great info!  Keep em coming!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.