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

1. Make sure the login(s) exists both on the primary and secondary server

2. Grant the login the relevant permissions on the relevant database(s), for example, db_datareader

3. 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:

1. Create two domain groups that will include the relevant end users (the groups will be identical)

2. 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

3. 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.

#highavailability #security #databasemirroring #AlwaysOnAG #mirroring #AlwaysOn