Fix Orphaned Users in a Database

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

Fix all orphaned logins in a database.

This usually occurs when restoring PROD DBs to another environment on a different server where the login SIDs do not match.

In early versions of SQL Server, this task used to require drop/recreate or a hit to the system tables directly, but now there is a system function

Code

CREATE TABLE #OrphanedUsers(

row_num INT IDENTITY(1,1),

username VARCHAR(1000),

id VARCHAR(1000)


)


INSERT INTO #OrphanedUsers(username,id)

EXEC sp_change_users_login 'Report'


DECLARE @rowCount INT ;


DECLARE @i INT;

DECLARE @tempUsername VARCHAR(1000);


SELECT @i =1 ;

SELECT @rowCount = (SELECT COUNT(1) FROM #OrphanedUsers );



WHILE(@i <= @rowCount)

BEGIN

SELECT @tempUsername = username FROM #OrphanedUsers WHERE row_num = @i;

EXEC sp_change_users_login 'Auto_Fix',@tempUsername;

SET @i = @i+1;

END


DROP TABLE #OrphanedUsers;