Removing orphaned users and logins from SQL Server

I’ve just spent some really fun hours trying to clean up users and logins from both our production and test-servers. Over the course of several years and restores from production to test, a number of orphaned users and logins have accumulated, that is, database users that do not have an associated login and logins that do not have any associated database users.

The following script attempts to identify both by extracting users (and user roles) from all databases and comparing them with the server logins. The output suggests the Sql statements to clean up the mess, but do peruse it throughly before executing!

Nuff said, here is the code:

Code Snippet
  1. — Script to assist cleaning up orphaned Logins and Users on Sql Server
  2.  
  3. — Query to extract Users and Roles
  4. DECLARE @sqlquery varchar(500)
  5. SET @sqlquery =
  6.     'SELECT DB_NAME() AS DatabaseName, P1.name AS UserName, P2.name AS UserRole FROM sys.database_role_members DRM '
  7.         +'JOIN sys.database_principals p1 ON P1.principal_id = DRM.member_principal_id '
  8.         +'JOIN sys.database_principals p2 ON P2.principal_id = DRM.role_principal_id '
  9.         +'WHERE P1.type IN (''S'', ''U'') AND LEN(P1.sid) > 1'
  10.  
  11. — Query to extract User-databases only, ie not master, tempdb etc
  12. DECLARE @dbname char(50)
  13. DECLARE c1 CURSOR READ_ONLY FOR
  14.     SELECT Name FROM sys.databases WHERE LEN(owner_sid) > 1
  15.  
  16. — Temporary table to hold all Database Users and Roles
  17. IF OBJECT_ID('tempdb..#USERNAMES') IS NOT NULL DROP TABLE #USERNAMES
  18.  
  19. CREATE TABLE #USERNAMES
  20. (
  21.     DatabaseName varchar(100),
  22.     UserName varchar(100),
  23.     UserRole varchar(100)
  24. )
  25.  
  26. — Loop over databases with a cursor
  27. OPEN c1
  28. FETCH NEXT FROM c1 INTO @dbname
  29.  
  30. WHILE @@FETCH_STATUS = 0
  31. BEGIN
  32.     PRINT 'Adding ' + @dbname
  33.     EXEC('USE [' + @dbname + ']; ' + 'INSERT INTO #USERNAMES ' + @sqlquery);
  34.     
  35.     FETCH NEXT FROM c1 INTO @dbname;
  36. END
  37. CLOSE c1 DEALLOCATE c1
  38.  
  39.  
  40. — Logins not used in any databases. NOTE a specific login is excepted
  41.  
  42. SELECT Name, 'DROP LOGIN [' + Name + '];' AS Sql
  43.     FROM sys.server_principals P
  44.     LEFT OUTER JOIN #USERNAMES U ON U.UserName = P.Name
  45.     WHERE type = 'S'AND is_disabled = 0 AND LEN(sid) > 1 AND U.DatabaseName IS NULL AND name NOT IN ('udvikler')
  46.  
  47. — Users in databases without server Logins. NOTE a specific login is excepted
  48.  
  49. SELECT DISTINCT DataBaseName, UserName, 'USE [' + DatabaseName + ']; DROP USER [' + UserName + '];' AS Sql
  50.     FROM #USERNAMES U
  51.     LEFT OUTER JOIN sys.server_principals P ON U.UserName = P.Name
  52.     WHERE name IS NULL AND UserName NOT IN ('dbo')
  53.  
  54. –SELECT * FROM #USERNAMES

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s