How to Grant Permissions Only on the Replica in Database Mirroring and AlwaysOn AG
You work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?
Scenario A: All databases are mirrored and should be accessed from the secondary
Make sure the login(s) exists both on the primary and secondary server
Grant the login the relevant permissions on the relevant database(s), for example, db_datareader
Delete the login from the primary server
This action will make sure a user with the relevant permissions exists at the database level, but the login exists only on the secondary, meaning the end user can connect and work only on the secondary.
Scenario B: Some databases should be accessed from the primary and some from the secondary
This is more complicated. For that, you’ll have to:
Create two domain groups that will include the relevant end users (the groups will be identical)
For one group, do the same trick as in scenario A. this will take care of the databases that should be accessed from the secondary
For the second group, grant the relevant permissions on the databases that should be accessed from the primary
And now you users can access the data only where you allow them.