How to give read permission on secondary replica in always-on AG
Purpose of Log shipping:
Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise and there are more features like read on secondary replicas.
Issue:
As we mentioned above, we can read secondary replica database in always-on AG, but we cannot give read permission on secondary replica database by creating login and user on secondary server database since the database will be read only status whic means readable yes.
Solution:
There are multiple steps to follow to give read permission on secondary replica database as discussed below
Steps1: Create login on primary replica server and create user on primary database with read permission
Step2: After creating login on primary replica server should wait untill datasync between primary replica and secondary replica
Step3: Once data sync is completed betwen primary replica and secondary replica the user will come to secondary replica 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.
