January 11, 2016 at 8:05 pm
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.
January 11, 2016 at 9:04 pm
I did more digging and found an answer here. Looks like I have to switch to replication to get what I am after.
January 11, 2016 at 11:40 pm
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
January 12, 2016 at 7:46 am
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" 😉
January 12, 2016 at 7:53 am
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.
January 12, 2016 at 8:00 am
lmarkum (1/12/2016)
1. Add Logins for BI team to primary instance2. 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