Finding orphaned database users

By Art on October 19, 2011

Database users can become orphaned for a few reasons. For example, a database restore to another instance or deleting the corresponding SQL login would have the effect of leaving a database user orphaned. Development/testing SQL servers may also experience this due to the number of ad-hoc updates that may occur over the years. If you are unlucky enough your production servers may even suffer from this.

SQL provides a procedure that will provide this information on a database level:

EXEC sp_change_users_login @Action='Report'

This is great and works well, however if you want to report across all of your databases, you’ll need to run this against each database in turn.

So here is a script that I sometimes use to search all non-system databases which aims to find any database users that have been orphaned or do not have a corresponding SQL server login. Armed with this information, you are then in a position to deal with any that the script finds. e.g. delete the user or link it using sp_change_users_login.

--Get a list of database names in a cursor that are not system databases and are online
    DECLARE cur CURSOR FAST_FORWARD FOR 
    SELECT name FROM sys.databases 
    WHERE database_id > 4 AND state = 0

    OPEN cur  

    DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME 
    DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))  

    FETCH NEXT FROM cur into @DBName  
    --loop through cursor, building a dynamic SQL statement that will USE the database and then retrieve the orphaned users
    --Execute the dynamic sql and store the results into a table variable
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, 
                UserName = name, UserSID = sid from sysusers 
                WHERE issqluser = 1 AND 
                    (sid IS NOT NULL AND sid <> 0x0) AND 
                    (LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'     

        INSERT INTO @Results 
        EXEC(@SQL)  

        FETCH NEXT FROM cur into @DBName  
    END  

    CLOSE cur 
    DEALLOCATE cur  

    SELECT * FROM @Results