MS-SQL 2014 Log Shipping: Access permission question

Question

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.

Answer 1

The people or security contexts running the expensive queries on the wrong instance should be identified and then notified to change their processes on the instance and DB to start running their stuff on the instance you want those run on. Since the secondary is read-only/standby, we would assume these expensive queries are SELECT statements only where the primary DB that's not in standby would have to run queries that update data or modify objects regardless of the expense. You could also look into why some of these queries are so expensive in the first place, see if adding indexes would help, rewriting queries for performance tuning, etc. I think that'd be a good root cause type solution.

It seems like it'd be tricky to do this with one of more AD groups though as the changes are replicated from primary to secondary and secondary would be in standby to accept transaction log changes from primary when those are applied so I'm not sure how you would accomplish this in that sort of configuration with log shipping.

I'm also not certain how often your tran logs are being restored to secondary as I thought when LSRestore jobs run, it disconnects all session on secondary until those transactions are committed, so I assume it's once a day or not too often or people would be screaming about their queries, etc. getting disconnected during normal hours. If once-a-day restores are occurring for LSRestore jobs on secondary, then this would mean the data gotten from that server is 24 hours old or however long between your LSBackup, LSCopy, and LSRestore jobs. So who can use which DB with this regard may depend on how fresh their query results need to be from the business side, etc.

Lots of factors to consider here but getting to the root cause and having bad performing queries tuned, adding indexes, etc. may be the best solution as well as having the people with access take responsibility with their processes to not hose up the performance for others when they run their stuff.

Answer 2

However this did not work as it denied WSG-1 access to SQL altogether on the primary server

This is because Denying CONTROL SERVER "DENY CONNECT SQL" permission implicitly denies CONNECT SQL permission on the server. A principal that is denied CONTROL SERVER permission on a server will not be able to connect to that server. Therefore the login is unable to connect to the server itself.

If you want the login not to access any specific database say database-A

1. Connect to your SQL server SSMS using management studio

2. Goto Security -> Logins -> [WSG-1]-> Make a right click on the login and click properties

3. Under User Mapping, un-check the database-A you want the login/WSG-1 not able to access.

Or you can do something like:

USE [Databasename_you_want_wSG-1_not to_access];
DENY VIEW DEFINITION TO [WSG-1];
GO

Answer 3

I think to accomplish this you would need another Windows group, say WSG-1Deny. Make WSG-1 a member of WSG-1Deny.

Add WSG-1Deny as a login on the primary and as a user in database-A. Add WSG-1Deny as a login on the secondary. Remove the WSG-1 user from database-A.

Wait until log shipping has copied the user and permissions for WSG-1Deny to the secondary. Now remove the login WSG-1Deny from the primary, the user will stay in the database, thus wont be changed on the secondary.

Now you should not have any access to database-A on primary for users in WSG-1 and have access on secondary for WSG-1Deny, which includes WSG-1.