May 30, 2014 at 11:12 am
Wanted to see what everyone else was doing about re-applying permissions after a restore from Production to a lower environment. So here is the scenario..
Ok, We take a backup from Production and restore it to Development. Since the Prod DB has totally different permissions than what a Dev DB would have what I want to do is wipe out all of the Permissions on the DB and re-apply only what should be valid for the Dev environment. So if I have default permissions for a DB I want all DBs to get the same permissions but if I have a DB in Dev that has special permissions other than what the default is I want to save them also as a different name like what the program name is. Also if I already have a DB lets say DB A and I restore another copy of production of A on the Dev server and I name it A_05302014 I want to apply the same permissions A already has.
Was thinking it would be great to store all of this in a DB then run a script against it to apply the permissions. If that is not an option then maybe just say scripts that do this.
I am not looking for scripts but advice on which direction to take and is it possible before I waste a lot of time..
Thanks
Scott
Jr. DBA
May 30, 2014 at 12:32 pm
Here's what I'd do first. Get every permission in a role. Once you have this, then make sure that users are only assigned roles, in both Dev and production. To make it easier, I'd use the same role names.
I'd script out the roles in each environment and store that in VCS as a controlled script of permissions for each role.
Then, when I restore in Dev, I'd probably delete the role and re-run the Dev Role script(s). You could easily do this in PS. I'd also have another script that assigns the appropriate logins to roles, creating users as needed.
May 30, 2014 at 1:52 pm
I could think of taking it one step further than Steve, and have exactly the same permissions in the two environments. The one difference when restoring the database to dev, I would add the developers to SuperDev role, which would be empty in the production database.
A great thing with this is that if the regular user permissions are present in Dev as well, you can easily test permissions in Dev.
If the developers are present in production, adding them to SuperDev is as easy as adding their Dev role to SuperDev. I suspect though that far from all developers have access to the production database, in which case you would need to have a script to add them to the Dev database.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply