How to give read permission on secondary server in log shipping
Purpose of Log shipping:
SQL Server Log shipping allows databases to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases, there are two modes which secondary will allow to keep the databases which are no_recovery mode and stand by mode.
Stand by mode will allow to read secondary database whereas no recovery mode will allow to restore log backups faster.
Issue:
As we mentioned above, we can read secondary database in log shipping, but we cannot give read permission on secondary database by creating login and user on secondary server database since the database will be on read only status.
Solution:
There are steps to follow to give read permission on secondary database as discussed below
Steps1: Create login on primary server and create user on primary database with read permission
Step2: After creating login on primary server execute the backup job and copy job and restore job
Step3: Once this jobs cycle completed the user will come to secondary database from primary server
Step4: Copy the Login SID from primary server
Step5: Create login on secondary server with same SID which is copied from primary server.
Step6: Delete the login on primary server. Leave the user as orphan user in primary database.
Note:
This issue may occur if it is sql authenticated logins only. If it is windows authentication login, we can skip the 4th step since SID will come from the windows not from the sql server.
