Pseudorandom Knowledge

Fix SQL Server login after backup

Ever had the scenario where there are two SQL servers, one production and one backup, then after backing up the databases to the backup server the logins don’t work anymore? This is because logins in SQL Server consists of two parts; one server login and one or more database users. After the backup the database users, coming from the production server, no longer matches the server logins, coming from the backup server.

To avoid this problem the backup server logins can be recreated (or created if they don’t exist yet) so that they match the production database users.

On the production server run the following query to find the name and SID (Security ID) of the login that you want to fix.

SELECT name, sid FROM sys.server_principals WHERE type = 's'

On the backup server start by removing the server login if it already exists.

DROP LOGIN Tusse

Then, on the backup server, create the user with the correct name, password and SID.

CREATE LOGIN Tusse WITH password = N'Pa$$w0rd', sid = 0x2D749BCC12...

Now the backup server logins will match the production database users even after future backups.