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;