How to restore orphaned users in TSQL after database restore

Use the following script

 

USE [master]

SET NOCOUNT ON;

-- Create a temp table to hold orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans

CREATE TABLE #Orphans (

      ID                INT NOT NULL IDENTITY,

      DBName            NVARCHAR(MAX) DEFAULT NULL,

      Username    NVARCHAR(MAX),

      userSID           NVARCHAR(MAX)

)

-- For each database get orphaned accounts

EXEC sp_MSforeachdb 'use [?]; insert into #Orphans (Username,UserSID) EXEC sp_change_users_login ''REPORT''; update #Orphans SET dbname = ''?'' where dbname is NULL'

-- Remove DBs that are not ONLINE or that are READ_ONLY as we can't alter them.

DELETE FROM #Orphans WHERE NOT [DBName] IN

(

SELECT name

FROM sys.databases

WHERE state_desc = 'ONLINE' AND is_read_only = 0 -- Databases that are ONLINE and NOT Read_Only

)

-- Loop through all remaining orphans and attempt to un-orphan them

DECLARE @CurRow   INT = 1

DECLARE @EndRow INT = (SELECT MAX(ID) FROM #Orphans)

DECLARE @SQLCmd   NVARCHAR(MAX)

WHILE @CurRow <= @EndRow

BEGIN

SET @SQLCmd = (SELECT 'USE [' + DBName + ']; EXEC sp_change_users_login ''UPDATE_ONE'', ''' + Username + ''', ''' + username + ''';' FROM #Orphans WHERE ID = @CurRow)

SET @CurRow = @CurRow + 1

PRINT @SQLCmd

EXECUTE (@SQLCmd) -- re associate the login with the user account

END

-- Drop temp table holding orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans