MS-SQL 2014 Log Shipping: Access permission 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.
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.