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; |
