Cool Tips‎ > ‎SQL Server Security‎ > ‎

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;





ċ
Andy Hughes,
Jun 9, 2020, 5:13 PM
Comments