Do your database users appear to be having an out of body experience?  Have they lost all ties to their login?

Does it feel like maybe what this baby rhino might be experiencing – just trying to find their place in your database?

Well, have we got a little snippet for you then.  This cure-all elixir … er script may be just what you need for those ghostly ghastly things called orphaned users.

Everybody has heard of them.  Everybody seems to want a quick fix for them.  But before you can fix your orphanage / database of these orphans – you have to know that they are there.

Cute Rhino

Well, not really – looks more like a hairless rat at this age.  But that is ok – I like rhinos of all ages.  But that is really beside the point.  I also like quick little scripts that will help me get a better picture of any databases that I may have inherited or that I must work on (which I had not previously worked on).

This script is naturally another one of those tools for the admin toolbox.  It is cursor based – but that is ok.  I might update it later to use a set based method and that new foreachdb that I have blogged about previously.

The reason for the looping is to run this quickly against all databases on an instance and immediately know where the orphans might be hiding.  This script is merely to report – it does not fix the orphans.  That is for you to work through.  Also of interest is that this script is not designed to work on SQL 2000 (it’s getting kinda up there in age anyway).

So here it is…another tool for the toolbox to help you become a better more efficient DBA.

Code block

CREATE TABLE #Orphans 
 (
  RowIDINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
  DBNameVARCHAR(100),
  OrphanUserVARCHAR (100),
  OrphanSidVARBINARY(85)
 )           
 
DECLARE
     @DBName SYSNAME,
     @SQL NVARCHAR(MAX) = N'';
 
DECLARE dbnams CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
     SELECT name
FROM sys.databases
WHERE state_desc NOT IN ('SUSPECT','OFFLINE')
AND name NOT IN ('tempdb','model')
 
OPEN dbnams
FETCH NEXT FROM dbnams INTO @DBName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT ''' + @DBName + ''' as DBName,dp.name AS OrphanUser, dp.sid AS OrphanSid
FROM [' + @DBName + '].sys.database_principals dp
LEFT OUTER JOIN sys.server_principals sp 
    ON dp.sid = sp.sid 
WHERE sp.sid IS NULL 
    AND dp.type_desc = ''SQL_USER''
    AND dp.principal_id > 4;'
 
INSERT INTO #Orphans
        ( DBName ,
          OrphanUser ,
          OrphanSid
        )
EXECUTE (@SQL)
 
FETCH NEXT FROM dbnams INTO @DBName;
 
END
CLOSE dbnams;
DEALLOCATE dbnams;
 
SELECT O.RowID,O.DBName,O.OrphanUser,O.OrphanSid
FROM #Orphans O
ORDER BY O.DBName,O.OrphanUser
 
DROP TABLE #Orphans;
GO