Sql-server – Grant Windows logins to read a secondary database of log shipping

Below steps should work :

1. Create windows login on ServerA
2. Create user in database mapped to login
3. Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
4. Grant any required permissions to user in database

5. Create login on log shipping ServerB

   • a. If using SQL authentication then create login while specifying SID from ServerA

   • b. If using Windows authentication, create login without specifying SID

      ---To query for the SID on the ServerA:
     
       Select SID
       From sys.database_principals
       Where name = 'Test'
     
      ---To create the login on the ServerB :
     
       Create Login [Test]
       With SID = '<put SID from query here>',
       Password = '<add password here>'
     

6. Make sure log shipping jobs have completed one full run since completion of step 4

Two options:

• On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
• Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH 
   PASSWORD = 'ThisIsAVerySecurePassword', 
   SID = 0x70DD0D59ADF3FD4ABB000037826A2888 

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)