Blog Post

Finding orphaned database users

,

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

Read 579 times
(1 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating