We have primary databases. They are in a mix of elastic pool, and standalone.
We have successfully created active geo replicated secondaries. (Our use for these is solely workload isolation.)
We want want to have different access policies. e.g A reader (by whatever principal – AAD group user etc) can only access the secondary.
This Microsoft Docs page implies it is possible to use different credentials for the secondary:
An application can access a secondary database for read-only
operations using the same or different security principals used for
accessing the primary database"
There are no further instructions. When attempting to use T/SQL grants on the secondary, an error is given that the DB is read only. This is expected behaviour, however the MS documentation quoted above suggest a different mechanism is possible.
How can we manage access at the secondary level, other than granting at the primary? Or granting excessive rights at the secondary, such as Active Directory Admin?
(We are aware of intent read only, but that does not meet our need, which is to deny certain principals access to the primary, regardless of intent)
Best Answer
The only way to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary-
read-only
orall
.