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
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Script to Find and Drop All Orphaned Users in All SQL Server Databases

MSSQLTips author Dale Kelly By:   |   Read Comments (7)   |   Related Tips: More > Scripts
Problem

One of my many day to day duties includes administering a database (actually many databases) for a Human Resources application. This application uses SQL logins for data access. This is a database I inherited so I had no input on how things were setup or administered. In the past the application administrator used a script to create new users but the script did not work well and frequently caused errors. When a user is created for the application a SQL login is created then a database user is created in many databases.

Since this script did not work well and I was the one that usually had to clean up the mess I decided to write a new script to create the users. After writing the script I also decided to write another script to delete users since the application did not provide a process for doing this. As I was writing the script to delete users it occurred to me that there may be orphaned users in many of the databases because deleting users has always been a manual process and it would be very easy to miss one. After checking a few databases and finding orphaned users I tried to find a script to clean up these orphaned users and was surprised that I could not find a script on the internet that did what I wanted so I decided to write my own and pass the information on to others.

Solution

What Is An Orphaned SQL User

So, what is an orphaned SQL user? An orphaned user is a database user that does not have an associated SQL login. There are a number of ways a user can become orphaned. The most common way is when a database from a different server is restored. When a database is backed up and restored the database users are also backed up and restored with the database, but the SQL login is not. If a database is restored to the same server the backup came from and the logins already exist then the database users will not be orphaned because security identifiers (SID) will be the same. If a database is restored to a different server and there are logins with the same name chances are the database users will be orphaned because the identifiers are not the same. And if the logins do not exist at all the database users will be orphaned. Another way database users can be orphaned is if the SQL login is deleted without checking for database users.

Find Orphans In All Databases

In my quest, I did find scripts but none did all that I wanted or did it the way I wanted. So I used some of these scripts as models and wrote my own script to get the results I wanted. My purpose in creating this script was twofold. First find all orphaned users in all databases in a server instance and second delete those users if desired. One difficulty encountered when deleting database users is that the user may own objects in the database and cannot be dropped until the object is dropped or ownership is transferred. In my case all users owned a schema so my script had to deal with a schema owned by the user. I wrote the script to store information about orphaned users in a local temporary table, then the temporary table could be used to drop the schema and user.

Find Orphaned Database Users Script

/*************************
*
* Script written by Dale Kelly 11/23/2011
* Revision 1.0
* Purpose: This script searches all databases for orphaned users
* and displays a list. If desired the orphaned users can be deleted
*
***********************/
Use master
Go
Create Table #Orphans 
 (
  RowID     int not null primary key identity(1,1) ,
  TDBName varchar (100),
  UserName varchar (100),
  UserSid varbinary(85)
 )
SET NOCOUNT ON 
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
 WHILE @DBName IS NOT NULL
 BEGIN
   SET @DBName = 
     (
  SELECT MIN(name) 
   FROM master..sysdatabases 
   WHERE
   /** to exclude named databases add them to the Not In clause **/
   name NOT IN 
     (
      'model', 'msdb', 
      'distribution'
     ) And 
     DATABASEPROPERTY(name, 'IsOffline') = 0 
     AND DATABASEPROPERTY(name, 'IsSuspect') = 0 
     AND name > @DBName
      )
   IF @DBName IS NULL BREAK
         
                Set @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName, 
                sid AS UserSID from [' + @DBName + ']..sysusers 
                where issqluser = 1 and (sid is not null and sid <> 0x0) 
                and suser_sname(sid) is null order by name'
 Insert into #Orphans Exec (@Qry)
 
 End
Select * from #Orphans
/** To drop orphans uncomment this section 
Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT 
from #Orphans
--Print @From
--Print @To
While @From < @To
 Begin
  Set @From = @From + 1
  
  Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
   Where RowID = @From
      
   Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
   print @DBsysSchema
   Print @DDBname
   Print @Orphanname
   set @SQL = 'If Exists (Select * from ' + @DBSysSchema 
                          + ' where name = ''' + @Orphanname + ''')
    Begin
     Use ' + @DDBName 
                                        + ' Drop Schema [' + @Orphanname + ']
    End'
   print @SQL
   Exec (@SQL)
     
    Begin Try
     Set @SQL = 'Use ' + @DDBName 
                                        + ' Drop User [' + @Orphanname + ']'
     Exec (@SQL)
    End Try
    Begin Catch
    End Catch
   
 End
**/
 
Drop table #Orphans

Things To Note About This Script

Databases can be excluded from the search by adding the database name to exclude in the 'NOT IN' where clause. If the "Drop Orphans' section is uncommented the script will drop any schema that the orphaned user owns then drop the orphaned user, there is no option to skip a schema/user. The script will not check for other objects that may be owned by the user. I have tested the script on SQL 2005 and SQL 2008 R2.

Next Steps
  • Copy the code above and paste into Notepad. Save it as a SQL script (.sql).
  • As always test the script in a test environment before using in production.
  • Execute the script on the server instance you want to check for orphaned users.
  • To drop the orphaned users uncomment the 'To drop orphans ...' section (Use with caution!).
  • Review the following resources:


Last Update: 2/10/2012


About the author
MSSQLTips author Dale Kelly
Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, February 10, 2012 - 6:09:50 AM - Issac Read The Tip

Thank You Dale for the script. It made my task simpler.


Monday, February 13, 2012 - 2:26:59 AM - Dirk Hondong Read The Tip

Hi Dale,

first of all: thank you for sharing.

Just a little reminder: you still need to review the output because you will also hit so called loginless users. Otherwise you would delete an account that´s probably in use.

Regards

Dirk


Monday, June 18, 2012 - 5:25:47 PM - Kristie Read The Tip

Would you know of a script I can run to determine when a user was added to a database?  We have 100+ databases we are managing and while we are able to see when a user was added to our application - and the user is usually given access to at least one database at the time of adding - we cannot determine when that user may have been added to any subsequent databases.  Do you know of a way?


Monday, June 18, 2012 - 5:48:24 PM - Greg Robidoux Read The Tip

@Kristie - you can query these system tables to find out when a database user was created.

select * from sys.sysusers
select * from sys.database_principals

You could run something like this to query all databases.

DECLARE @command varchar(1000)
SELECT @command = 'USE [?] SELECT db_name(), name, create_date from sys.database_principals '
EXEC sp_MSforeachdb @command


Wednesday, October 02, 2013 - 7:33:46 PM - Julio del Aguila Read The Tip

Excellent!!! Thanks for your help, very useful.

Regards.


Thursday, March 13, 2014 - 3:02:23 AM - Stef Read The Tip

When the username is vert long (25 characters or more), thefollowing statement will fail:

 

set @SQL = 'If Exists (Select * from ' + @DBSysSchema
                          + ' where name = ''' + @Orphanname + ''')
    Begin
     Use ' + @DDBName
                                        + ' Drop Schema [' + @Orphanname + ']
    End'

 

Solution:

Change Declare @Orphanname varchar (100) into Declare @Orphanname varchar (200)


Thursday, March 13, 2014 - 3:04:37 AM - Stef Read The Tip

Addition to my previous comment, you also need to extend the definition for "Declare @SQL as varchar (200)" to like 400 or more.

 



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.