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.
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.
CREATE TABLE #Orphans ( RowID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , DBName VARCHAR(100), OrphanUser VARCHAR (100), OrphanSid VARBINARY(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