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)BEGINSELECT @tempUsername = username FROM #OrphanedUsers WHERE row_num = @i;EXEC sp_change_users_login 'Auto_Fix',@tempUsername;SET @i = @i+1;ENDDROP TABLE #OrphanedUsers;