Permissions on Log Shipping Secondary

  • Is there any way to allow people to query a log shipping secondary but deny permission to query the primary? If I remove permission in the primary then that change goes over to the secondary. I am trying to prevent the BI team from querying the primary when the secondary has not finished restoring.

  • I did more digging and found an answer here. Looks like I have to switch to replication to get what I am after.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47434c9b-f000-4a59-8a43-9d05bc4cc8f2/separate-permissions-on-log-shipping-primary-and-secondary-databases?forum=transactsql

  • Database grants, revokes and denies are logged so with Log Shipping they are applied to the Secondary. Replication allows you to have mismatched permissions and even mismatched schemas and data between the publisher and subscriber. Replication is far more flexible for offloading reads but a lot more work to setup and admin in my experience.

    You could potentially control access to the primary instance by removing the Logins from the primary instance but this would only work if the BI team needed no access to any databases on the primary. It would go like this:

    1. Add Logins for BI team to primary instance

    2. Add Users to relevant databases for BI Team Logins and grant permissions to new Users

    3. Drop Logins from primary instance <- this prevents the BI team from connecting to the primary

    4. Add Logins to secondary instance <- this allows the BI team to connect to the secondary

    For step 4 if these are SQL Logins and not Windows Logins you just need to make sure you keep the SIDs the same.

    Another option is to standup a pass-through database on the instance where the log shipped secondary lives and front the tables you want to allow the BI team access to with objects that do cross-database calls. There are some restrictions on the types of objects you can implement in the front-database and some extra steps to make it work related to signing the objects.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • dropping or disabling the logins on the primary would get you round this

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 1. Add Logins for BI team to primary instance

    2. Add Users to relevant databases for BI Team Logins and grant permissions to new Users

    3. Drop Logins from primary instance <- this prevents the BI team from connecting to the primary

    4. Add Logins to secondary instance <- this allows the BI team to connect to the secondary

    So the BI team already has a login on the Secondary. The drop for the Login on the Primary isn't pushed to the secondary is it? Essentially I am creating an orphaned database user in the primary with this process.

  • lmarkum (1/12/2016)


    1. Add Logins for BI team to primary instance

    2. Add Users to relevant databases for BI Team Logins and grant permissions to new Users

    3. Drop Logins from primary instance <- this prevents the BI team from connecting to the primary

    4. Add Logins to secondary instance <- this allows the BI team to connect to the secondary

    So the BI team already has a login on the Secondary. The drop for the Login on the Primary isn't pushed to the secondary is it?

    No, Logins are at the instance-level.

    Essentially I am creating an orphaned database user in the primary with this process.

    Correct but you can still continue to grant the User permissions on the primary and they will flow to the secondary.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply