Separating permissions between live and log shipped instances RRS feed

  • Question

  • Hi all,

    I've got a requirement to engineer permissions for a group of users in an environment I support. 

    Instance A hosts multiple DBs, one of which is log shipped to a reporting server, Instance B.  We're trying to force users to run reports against the reporting (log shipped) Instance B for performance reasons. 

    I can't remove their read access at the DB level on Instance A as I understand it, as doing so would remove their access to the Reporting Instance B when the change is log shipped over.  

    I can't remove their read access at Instance A level as the users also need access to other DBs hosted by that instance.

    One of the customer's application managers with some SQL experience has mentioned using SQL roles to restrict access to the specific tables on Instance A only.  Is this possible?  Can someone point me at an article describing this?  My Bing-Fu and Google-Fu have failed me miserably so far.

    Thanks in advance for any pointers.


    Friday, May 17, 2019 5:24 PM

All replies

  • No, in the general case, that is not possible, since with log shipping the log-shipping database is a perfect replica of the source database. So all roles and permissions will be the same on both instances. The only thing you can play with is the permissions on server level, but this seems also to be out of reach for you.

    There may however be lucky circumstancess that permits for a solution.

    One is if all reports are based on stored procedures that are only used for these reports. In this case, these procedures could make a check on the hostname, possibly in combination with check for a role that identifies these users.

    A logon trigger can also be an option. This assumes that the report tool connects directly to this database and that users cannot first connect to another database and then change to this database in the report tool. In this case, the logon trigger could check for the combination of database and user and stop them at the gate.
    I'm assuming the users we are talking about are Windows logins. Would they be SQL logins, there is also a solution - make sure that SIDs are different on the two instances.

    If none of these fits you, maybe you should consider transactional replication instead. It is more complex to set up and monitor, but there is no issues with having to kick out users as you apply new logs. And you can configure users and permissions locally.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, May 19, 2019 6:45 PM