November 3, 2014 at 12:46 pm
Hey guys, I have a database on a server owned by another team that I use as a datasource in my non prod enviroment when developing packages. Occasionally they refresh their database with whats on Production. Is there a way I can enure when this happens my login doesn't lose its read access?
November 3, 2014 at 1:07 pm
When this database gets restored from production, all the permissions as they are in production will be restored with it. As a DBA what I typically do to handle this is have a SQL Agent job in my development and test instances to perform the steps of restoring the backup and performing any security and other data changes to make this database usable in its new environment. You might want to check with that other group if they could add a step in their process to add your read privileges back in after the restore is done.
November 3, 2014 at 1:09 pm
Ask the person to secure the user permissions before refreshing the database and reapply the permissions once it is done. Any DBA should be aware of how to do that...
November 3, 2014 at 3:38 pm
Does that user/login also exist in prod? If so, the SIDs of the prod login and the nonprod login are different. If you want the user to work automatically, easiest is to force the SID on your nonprod server to be the same as on your prod server. Then SQL will re-sync the user with the login itself. If you want to pursue that, let me know: you just have to drop that login, recreate it, and re-sync that login to its user(s) once on your nonprod server.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply