SQL Server – Orphaned Users

SQL Server – Orphaned Users

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.
A database user can become orphaned if the corresponding SQL Server login is dropped.
Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server.
Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance

Useful Tables and Stored Procedures

--First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'

-- If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'

-- If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Orphaned Users Reports including Windows Logins

-- Report Orphaned Users Including Windows Users for a Given Database
SELECT p.name
FROM sys.database_principals AS p
WHERE [type] IN ( N'U', N'G' )
AND NOT EXISTS ( SELECT 1
FROM sys.server_principals AS sp
WHERE sp.sid = p.sid );
GO

-- Report Orphaned Users Including Windows Users for a All Databases
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + db.name + ''',p.name
FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
WHERE [type] IN (N''U'', N''G'')
AND NOT EXISTS
(
SELECT 1 FROM sys.server_principals AS sp
WHERE sp.sid = p.sid
);'
FROM sys.databases AS db
WHERE [state] = 0;

SELECT @sql

EXEC sp_executesql @sql;

Drop Orphaned Users

-- Report Orphaned Users Including Windows Users for a Given Database
SELECT ' DROP USER ' + QUOTENAME(p.name)
FROM sys.database_principals AS p
WHERE [type] IN ( N'U', N'G' )
AND NOT EXISTS ( SELECT 1
FROM sys.server_principals AS sp
WHERE sp.sid = p.sid );

Drop orphaned users who doesn’t have any dependencies.
If they have dependencies, you need to drop the dependencies explicitly before dropping the orphaned users.
Otherwise , you will encounter errors like this.

/*
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
*/

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

OPEN cur;

DECLARE @SQL AS NVARCHAR (MAX),
@DBName AS SYSNAME,
@pUserName NVARCHAR(255)

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
        EXECUTE (@SQL);
        FETCH NEXT FROM cur INTO @DBName;
    END

CLOSE cur;

DEALLOCATE cur;

SELECT *
FROM   @Results


DECLARE c CURSOR FOR
SELECT DBName , UserName
FROM @Results
WHERE UserName NOT IN ('dbo')

OPEN c
FETCH NEXT FROM c INTO @DBName , @pUserName

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @SQL =  'USE ' + @DBName + CHAR(13)+
				'DROP USER  ' + '['+@pUserName +']'+ CHAR(13)
	PRINT @SQL
	EXEC (@SQL)
	FETCH NEXT FROM c INTO @DBName , @pUserName

END
CLOSE c
DEALLOCATE c