“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]
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.