{"id":173,"date":"2014-12-19T16:20:39","date_gmt":"2014-12-19T15:20:39","guid":{"rendered":"http:\/\/www.codereview.co\/?p=173"},"modified":"2014-12-18T12:25:45","modified_gmt":"2014-12-18T11:25:45","slug":"a-script-to-finddrop-all-orphaned-users-in-sql-server-databases","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/a-script-to-finddrop-all-orphaned-users-in-sql-server-databases\/","title":{"rendered":"A script to find\/drop all orphaned users in SQL Server Databases"},"content":{"rendered":"

Scenario Proposal<\/strong><\/span><\/p>\n

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.<\/span><\/p>\n

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.<\/span><\/p>\n

 <\/p>\n

Solution<\/strong><\/span><\/p>\n

What Is An Orphaned SQL User?<\/span><\/h5>\n

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.<\/span><\/p>\n

Find Orphans In All Databases<\/span><\/h5>\n

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.<\/span><\/p>\n

Find Orphaned Database Users Script<\/span><\/h5>\n

Use master<\/em><\/span>
\nGo<\/em><\/span>
\nCreate Table #Orphans <\/em><\/span>
\n\u00a0(<\/em><\/span>
\n\u00a0 RowID\u00a0\u00a0\u00a0\u00a0 int not null primary key identity(1,1) ,<\/em><\/span>
\n\u00a0 TDBName varchar (100),<\/em><\/span>
\n\u00a0 UserName varchar (100),<\/em><\/span>
\n\u00a0 UserSid varbinary(85)<\/em><\/span>
\n )<\/em><\/span>
\nSET NOCOUNT ON <\/em><\/span>
\n\u00a0DECLARE @DBName sysname, @Qry nvarchar(4000)<\/em><\/span>
\n SET @Qry = ”<\/em><\/span>
\n SET @DBName = ”<\/em><\/span>
\n WHILE @DBName IS NOT NULL<\/em><\/span>
\n BEGIN<\/em><\/span>
\n\u00a0\u00a0 SET @DBName = <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0(<\/em><\/span>
\n\u00a0 SELECT MIN(name) <\/em><\/span>
\n\u00a0\u00a0\u00a0FROM master..sysdatabases <\/em><\/span>
\n\u00a0\u00a0\u00a0WHERE<\/em><\/span>
\n\u00a0\u00a0 \/** to exclude named databases add them to the Not In clause **\/<\/em><\/span>
\n\u00a0\u00a0 name NOT IN <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0(<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 ‘model’, ‘msdb’, <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘distribution’<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0 ) And <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0DATABASEPROPERTY(name, ‘IsOffline’) = 0 <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0AND DATABASEPROPERTY(name, ‘IsSuspect’) = 0 <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0AND name > @DBName<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/em><\/span>
\n\u00a0\u00a0 IF @DBName IS NULL BREAK<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Set @Qry = ‘select ”’ + @DBName + ”’ as DBName, name AS UserName, <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sid AS UserSID from [‘ + @DBName + ‘]..sysusers <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0where issqluser = 1 and (sid is not null and sid <> 0x0) <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and suser_sname(sid) is null order by name’<\/em><\/span>
\n Insert into #Orphans Exec (@Qry)<\/em><\/span>
\n\u00a0<\/em><\/span>
\n\u00a0End<\/em><\/span>
\nSelect * from #Orphans<\/em><\/span>
\n\/** To drop orphans uncomment this section <\/em><\/span>
\nDeclare @SQL as varchar (200)<\/em><\/span>
\nDeclare @DDBName varchar (100)<\/em><\/span>
\nDeclare @Orphanname varchar (100)<\/em><\/span>
\nDeclare @DBSysSchema varchar (100)<\/em><\/span>
\nDeclare @From int<\/em><\/span>
\nDeclare @To int<\/em><\/span>
\nSelect @From = 0, @To = @@ROWCOUNT <\/em><\/span>
\nfrom #Orphans<\/em><\/span>
\n–Print @From<\/em><\/span>
\n–Print @To<\/em><\/span>
\nWhile @From < @To<\/em><\/span>
\n Begin<\/em><\/span>
\n\u00a0 Set @From = @From + 1<\/em><\/span>
\n\u00a0 <\/em><\/span>
\n\u00a0\u00a0Select @DDBName = TDBName, @Orphanname = UserName from #Orphans<\/em><\/span>
\n\u00a0\u00a0 Where RowID = @From<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 <\/em><\/span>
\n\u00a0\u00a0\u00a0Set @DBSysSchema = ‘[‘ + @DDBName + ‘]’ + ‘.[sys].[schemas]’<\/em><\/span>
\n\u00a0\u00a0 print @DBsysSchema<\/em><\/span>
\n\u00a0\u00a0 Print @DDBname<\/em><\/span>
\n\u00a0\u00a0 Print @Orphanname<\/em><\/span>
\n\u00a0\u00a0 set @SQL = ‘If Exists (Select * from ‘ + @DBSysSchema <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ ‘ where name = ”’ + @Orphanname + ”’)<\/em><\/span>
\n\u00a0\u00a0\u00a0 Begin<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0 Use ‘ + @DDBName <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ ‘ Drop Schema [‘ + @Orphanname + ‘]<\/em><\/span>
\n\u00a0\u00a0\u00a0 End’<\/em><\/span>
\n\u00a0\u00a0 print @SQL<\/em><\/span>
\n\u00a0\u00a0 Exec (@SQL)<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0 <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0Begin Try<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0 Set @SQL = ‘Use ‘ + @DDBName <\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ ‘ Drop User [‘ + @Orphanname + ‘]’<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0 Exec (@SQL)<\/em><\/span>
\n\u00a0\u00a0\u00a0 End Try<\/em><\/span>
\n\u00a0\u00a0\u00a0 Begin Catch<\/em><\/span>
\n\u00a0\u00a0\u00a0 End Catch<\/em><\/span>
\n\u00a0\u00a0 <\/em><\/span>
\n\u00a0End<\/em><\/span>
\n**\/<\/em><\/span>
\n\u00a0<\/em><\/span>
\nDrop table #Orphans<\/em><\/span><\/p>\n

\u00a0Things To Note About This Script<\/span><\/h5>\n

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.<\/span><\/p>\n

 <\/p>\n

 <\/p>\n

Thanks for reading this article,<\/strong><\/span><\/p>\n

Next steps :<\/strong><\/span><\/p>\n

    \n
  1. Add this script to your database toolkit<\/strong><\/b><\/span><\/li>\n
  2. Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n
  3. Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Scenario Proposal 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[30,29,27,28,13,12],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/173"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=173"}],"version-history":[{"count":2,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/173\/revisions"}],"predecessor-version":[{"id":175,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/173\/revisions\/175"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=173"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}