April 23, 2019 at 3:37 pm
Hello --
I should know this... but is there any way to grant a user global read-only access?
We replicate our production server using log shipping, and we use the replicated copy for reporting purposes. We have a few users who we would like to give read-only access to this copy. However, the databases are all unchangeable since they're being log-shipped; we cannot add the users to them without doing so in production - which we do not want to do.
I know that we could make them all sysadmin but that isn't an option for obvious reasons.
April 23, 2019 at 4:06 pm
You can't database permissions at server level, no. sysadmin is the only real "exception" to that rule, but they still end up effectively being mapped the to db owner.
Do these users have any permissions on your production server? If not, have you considered creating an AD group for them, and create the AD group users on the relevent databases on the [production] server, with no login? That way they still won't be able to gain access to the server. Then you could create the AD group a login on the server you are replicating to and then would have access.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 23, 2019 at 9:11 pm
Kinda what I figured... but nice to have confirmation 😀
They do have access to the production server to read and write from various databases - but it isn't global.
That being said, the people in question are technically allowed (from a business perspective) to have read-only access to all company data; we mainly just didn't want them logging into production and running expensive queries. So I think the AD group might be the best solution... We can block them from connecting via specific tools in production (Office products, Management Studio, etc.) to prevent that sort of thing.
I was hoping to avoid messing with AD (our parent company controls our AD and they're kinda strict about it) but I can probably make it work.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply