Restricted user access only on main replica?

Question

We have an Availability Group (AOG) on SQL Server 2019 with two replicas (primary and secondary). The database within the AOG is set to restricted user access mode on the primary node (read-write). Can this database be in "multi-user access" mode on the secondary node (read-only)?

Answer 1

the permissions that live inside the database those will be the same in all replicas, however, you can play with the part of the security that is outside the database, for instance the login.

one thing I have done in the past is:

1. create a login in the primary and in the secondary lets say my_reader
2. grant my_reader all the permission he needs in both primary and secondary
3. as you said you want to restrict access to primary you then remove or disable the login in the primary but keep it in the secondary

in that way users could use that my_reader login to connect and read from the secondary, but they would not be able to connect to the primary.

you will have to deal with failovers, and listeners. that for now I leave it for you.

failover, you can always have a job checking if you are in the primary, and so, enable that login, otherwise disable it.