Cleaning up orphaned users in SQL Server

Short blog post here; I’m working with my team to clean up some older SQL Servers, and we’re removing several logins that have been dormant or disabled for some time.  Removing the logins is easy, but it leaves orphaned users in several of our databases.  I created the script below to cycle through each of the databases on the server and generate a series of DROP USER commands for each database.

DECLARE @t TABLE
    (
      db VARCHAR(100)
    , u VARCHAR(1000)
    , s INT
    )

DECLARE @sql NVARCHAR(4000)
SET @sql = 'USE ?;

SELECT  DB_NAME()
      , dp.name
      , s = CASE WHEN s.Schema_id IS NOT NULL THEN 1
                 ELSE 0
            END
FROM    sys.database_principals AS dp
        LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
        LEFT JOIN sys.schemas s ON s.principal_id = dp.principal_id
WHERE   dp.type IN ( ''U'', ''S'' )
        AND sp.sid IS NULL
        AND dp.authentication_type IN ( 1,  3 )
        AND dp.name <> ''dbo'''

INSERT  INTO @t
        EXEC sp_msforeachdb @sql

SELECT  'USE ' + db + '; '
        + CASE WHEN s = 1
               THEN 'ALTER AUTHORIZATION ON SCHEMA::[' + u + '] TO dbo; '
               ELSE ''
          END + ' DROP USER [' + u + ']'
FROM    @t

If the script discovers an orphaned user, the output will look something like:

USE AdventureWorks2012; DROP USER [olduser]

If that user owns a schema in the database, an ALTER AUTHORIZATION step is added to first transfer the schema to dbo, and then drop the user:

USE AdventureWorks2012; ALTER AUTHORIZATION ON SCHEMA::[olduser] TO dbo; DROP USER [olduser]

I’m sure there are other ways to accomplish the same thing, but it works well for this task.

October 2, 2013 · stuart · 4 Comments
Tags: , ,  Â· Posted in: SQL, SQL Server, SQLServerPedia Syndication

4 Responses

  1. Tran - February 21, 2014

    Do you have a script that will do the same exact thing but in 2008 R2? I know this only works for SQL2012 as the column authentication_type does not exist in table sys.database_principals in 2008R2.

    Thanks

  2. stuart - February 21, 2014

    Tran, I don’t have a SQL 2008 instance to test this on, but I think you can just comment out that line in the WHERE clause. Looking at the documentation for sys.database_principals for SQL 2012 appears to use that column to differentiate between traditional server logins (SQL and Windows) and contained databases (introduced with SQL 2012). In other words, I think the older versions of this table assume that the only database principals that exists are either a SQL login or a Windows Login.

  3. Tran - February 24, 2014

    Thank you for you quick reply. I have already tried to remove that line from the WHERE clause which didn’t work. All I got from the result are NULL’s and it didn’t remove any orphan users.

  4. stuart - March 5, 2014

    AHHHHH, a friend of mine found it for me; there was an error in my code. Instead of returning sp.name, it should be dp.name in the script (I already cleaned it up in the code above). That, combined with the other changes, should fix the issue in SQL 2008.

Leave a Reply