Watch Your Step: Log Shipping With Read-Only Secondary

In my opinion, Log Shipping is one of the easier options to setup when it comes to disaster recovery. Log Shipping can provide a warm copy of the primary database and can also off load reporting using the “WITH STANDBY” option. It is fairly simple to setup, and you can find all kinds of guides to assist you like this one from TechNet, but like most options, there are a few gotchas. One that might trip you up involves setting up the secondary as read-only and login mapping.

For example, suppose you have two servers, ServerA and ServerB, both with a SQL login called Reporter. You want to create a database on ServerA and then use log shipping to create a warm standby of that database and off load reporting on ServerB. If you follow the steps from the TechNet article, you should have the Log Shipping up in no time, but when you go to query the read-only copy on ServerB using the Reporter login, you will be unable to query the database due to permissions.

The reason for the permissions issue is the Reporter login on the ServerB is not mapped to the Reporter user on the secondary copy because the SID of the login doesn’t match the SID of the user. When you initialize the secondary database, you restore a full backup from the primary database, which contains the Reporter user but with the same SID as Reporter login on ServerA. So in order to get the Reporter login on the ServerB to map to the Reporter user on the secondary copy, you will need to create the login with the same SID as the login on the primary server.

To do this, you will first need to get the SID of the Reporter login on ServerA by running the following query on ServerA.

    SELECT SID FROM sys.sql_logins WHERE name = ‘Reporter’

Once you have the SID, you can then copy it and paste it to the query below. Finally, run the query on ServerB.

    CREATE LOGIN Reporter WITH PASSWORD = ‘JustTheFacts’, SID = 0x9B83D77ECD9114469307CD527B8E6D2B

This will create the Reporter login on ServerB with the same SID as the Reporter login on ServerA. Now, when you initialize the ServerB and restart Log Shipping using the with standby option to make it read only, the SID of the Reporter user on the secondary copy will match the SID of the Reporter login, allowing the user to connect and read from the log shipped copy.