Wonder if someone could provide some advice or point me in the right direction towards achieving the below objective please. Unfortunately I have not been very successful in finding a solution online.
We are at the process of implementing Log shipping between 2
SQL-2014 servers. The user access are enabled on our SQL servers via Windows Security Groups.
We would like one
Windows Security Group (eg: WSG-1) of users running expensive queries be denied access only to say a single database (eg: database-A) on the primary server but be allowed access to:
1). The rest of the databases on the primary server and
2). The same database-A (standby/read-only) on the secondary server.
My initial plan was to deny SQL connect access to the WSG-1 on the primary server but grant WSG-1 all access on the secondary server. However, this did not work as it denied WSG-1 access to SQL altogether on the primary server. I used:
DENY CONNECT SQL TO [DOMAIN\WSG-1]
Wonder if a solution could be accomplished this way or if there is a different way to a solution.
The biggest challenge I find with Log-shipping is, any grant/deny applied at database level on the primary server is mirrored exactly over to the secondary server with the subject database left in non recovery mode. But we would like to vary the db level access permission between the primary and the secondary server for the subject database.
I find that the database role denied access on the primary server remain denied on secondary too for the group WSG-1. Not sure if my problem could be approached in a different way.