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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s