May 9, 2019 at 12:00 am
Comments posted to this topic are about the item A Self-Serve System that Allows Developers to Refresh Databases (Part 2)
May 16, 2019 at 3:46 pm
Great article! I've implemented similar mechanisms and also solved the backup security problem (how to avoid granting backup/restore rights to developers) by polling a status table from a Server Agent job.
One step I've always included in my implementation is updating security on the newly refreshed test server database. I think it's safe to say that most shops limit or even exclude developer permissions on production servers and also don't allow any end-user connections to test servers. Additionally, there are still systems out there that require the use of SQL Server logins. Both of those scenarios might require that users be dropped and created on the newly refreshed test database along with permission re-assignments.
I know that ideally the prod/test servers won't have logins from other environments, so it's possible to simply accept existence of "orphan" database users as benign, but that practice puts you just one step away from inadvertently granting incorrect database access.
So, I'm just curious if you implement any security adjustments after a refresh and, if so, how do you maintain and execute scripts that may have to be tailored for each database?
May 16, 2019 at 4:28 pm
That is a really good question, I am glad you asked it. In my case we have pushed and force things to be only active directory on the security side. So developers exist in an AD group and that AD group has dbo rights on the Test database. If I had to do special security stuff, I would just add a job step that would call or execute the appropriate script to ensure the security was setup properly.
Sorry, I don't have a cool better way of solving this issue.
Ben
May 16, 2019 at 6:38 pm
Sorry, I don't have a cool better way of solving this issue.
LOL. Not sure there's a cool way to solve it!
Regarding the developer AD group: Do you grant it those dbo rights on the Test server after doing the database refresh, or does the AD group already have those rights as copied from production? If those rights are in the production DB, but not available because the AD login doesn't exist on the production server, there's a chance the developer AD login might get added later on for some unrelated reason (eg. to grant developers read access in production to a centralized error logging database). If that happened, developers would gain dbo rights on the production database.
I'm not trying to be being overly critical here - setting up a self-serve refresh system can save DBAs countless tedious hours of manually doing back-up/restores. I'm just pointing out enhancements that others may want to add prior to implementation. One consideration might be to add a table to your suggested DBCopy database that contains a DBName column and a PostRefreshScript column. Then the SA job could check for DBName entries matching the refresh request and run any post refresh scripts that it finds. That enhancement would also allow for scripting other steps such as randomizing sensitive data or adding test records.
May 16, 2019 at 6:43 pm
Your point is valid. We have multiple AD groups, so it could be possible to have the wrong one have rights on production. So far that has not been the case.
I do like your idea of having a postrefreshscript column. I think that is a really good suggestion.
Thanks,
Ben
May 26, 2019 at 9:51 am
Great job!
In the usp_KillConnections procedure, why not turn the DB to SINGLE_USER ?
ALTER DATABASE [DB NAME] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
This will remove all current connections,
besides the SPid that your using for the single user, and you can run the backup from it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply