CREATE PROCEDURE dbo.MapUsersToLogins AS /********************************************************************** Tomas Lind 2014 http://media.tomaslind.net/2014/02/MapUsersToLogins.txt Use after a database restore to map orphaned users to logins. The names of the user and login must match. Run in the restored database. **********************************************************************/ SET NOCOUNT ON DECLARE @USERS_TO_MAP_TO_LOGINS TABLE ( RowNo INT IDENTITY(1,1) NOT NULL ,UserName SYSNAME NOT NULL ) DECLARE @TotalRows INT = 0 ,@CurrentRow INT = 1 ,@SQLString NVARCHAR(MAX) INSERT @USERS_TO_MAP_TO_LOGINS (UserName) SELECT DP.name FROM sys.database_principals DP JOIN sys.server_principals SP ON DP.name = SP.name AND DP.[sid] <> SP.[sid] --IF THE SID'S ARE THE SAME THEY ARE ALREADY MAPPED WHERE DP.type <> 'R' --FILTER OUT ROLES AND DP.principal_id > 4 --FILTER OUT USERS CREATED BY DEFAULT SELECT @TotalRows = COUNT(*) FROM @USERS_TO_MAP_TO_LOGINS SELECT * FROM @USERS_TO_MAP_TO_LOGINS WHILE @CurrentRow <= @TotalRows BEGIN SELECT @SQLString = 'ALTER USER ' + UserName + ' WITH LOGIN = ' + UserName FROM @USERS_TO_MAP_TO_LOGINS WHERE RowNo = @CurrentRow EXEC sp_executesql @SQLString SET @CurrentRow = @CurrentRow + 1 END