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