4

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.

1
  • You should create a role and add the windows group to the role. Then just deny permissions to the role. See my answer as an example. This way you just add/remove users from the role which is much more manageable than granting and denying permissions to different accounts.
    – Kin Shah
    Aug 12 '15 at 14:39
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.

0

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
10
  • Doing this will also remove access on the secondary instance for that database.
    – Bob Klimes
    Aug 12 '15 at 14:43
  • 1
    You should be able to add the access back on the secondary instance at the end of the end of the transaction restore script.
    – Aaron
    Aug 12 '15 at 14:50
  • @aaron, you cannot make any changes in secondary database only as it is read only.
    – Bob Klimes
    Aug 12 '15 at 14:51
  • 1
    @BobKlimes you can if you create login with the same SID as primary
    – Kin Shah
    Aug 12 '15 at 14:53
  • @kin, the same windows group is being used for all other databases on the primary. The only way you can restrict permission on primary while retaining permission on secondary is removing login while leaving user in the database. if login is removed, access to all other databases is also removed which is not what the OP wanted.
    – Bob Klimes
    Aug 12 '15 at 14:57
0

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.

10
  • Thanks @BobKlimes. Just to clarify, the gist of what you have suggested is: Map database-A only to the group WSG-1Deny (say as db_datareader) which in-turn is the parent of the group WSG-1, then remove the group WSG-1Deny from the server Logins while leaving WSG-1Deny intact under database-A. Aug 13 '15 at 11:16
  • If so, I tried the above and what I find is, when users of the group WSG-1 connect to SQL, SQL sees their windows Login names (eg: DOMAIN\DodgyUser1) which is evident from sp_who/sys.dm_exec_sessions. On this basis, SQL appears to let DOMAIN\DodgyUser1 access database-A as he/she is by descent also a member of DOMAIN\WSG-1Deny in-spite of the missing server level login for WSG-1Deny. Wonder if I am doing this correctly. Aug 13 '15 at 11:17
  • I was afraid that might be the case. I dont have access to our AD to have been able to test this. I have generally only done this with individual accounts and I never had the requirement of only some databases on the primary be accessible.
    – Bob Klimes
    Aug 13 '15 at 13:38
  • another possibility would be to use SQL logins, same procedure as above but SQL logins would need the same SID(link in comment from other answer). then on secondary grant permission to WSG-1 to impersonate the SQL login and run all queries with "execute as login = "SQLLogin" "
    – Bob Klimes
    Aug 13 '15 at 13:55
  • Thanks @BobKlimes. I am happy with the idea of using a SQL login for impersonation. In which case, can WSG-1 be set/mapped to impersonate the SQL login on the secondary server itself so that security impersonation is transparent to the users when they connect to SQL. Cheers Aug 13 '15 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.