SQL Server – View Orphaned Users

Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.

Below is a script that I use to view orphaned users.

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 

OPEN cur  

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

FETCH NEXT FROM cur into @DBName  

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

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter