Blog

“Why can’t I login after the database restore?”

As a DBA, this is one of the questions I get most.

The answer is simple. Users exists in the database. Logins exists on the server. When a database is restored, so are the users. But the logins aren’t touched, since they are on the server level. What happens is that SQL Server loses the link between users and logins. Those users are called orphaned users.

To map the user to a login, simply issue the statement:

ALTER USER [username] WITH LOGIN = [loginname]

The ALTER USER command is used instead of the old sp_change_users_login in SQL Server 2005. More about the ALTER USER command can be found on TechNet.

If you have your restores automated, the script below can be run after each restore to map users to logins. Although, the user name must match the login name for the script to work. There is no way to know about the lost connection between a user and a login if the names don’t match.

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

Remember to test the script so that it works as you expect.

In SQL Server 2012 and later, there is a solution to the problem with orphaned users. In SQL Server 2012 and later, you can use Contained Databases, which means that user authentication can be done by the database without the assistance of logins on the SQL Server instance level.