A Self-Serve System that Allows Developers to Refresh Databases (Part 2)

  • bkubicek

    SSChampion

    Points: 10735

    Comments posted to this topic are about the item A Self-Serve System that Allows Developers to Refresh Databases (Part 2)

  • Andy DBA

    SSCommitted

    Points: 1776

    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?

     

  • bkubicek

    SSChampion

    Points: 10735

    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

  • Andy DBA

    SSCommitted

    Points: 1776

    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.

     

  • bkubicek

    SSChampion

    Points: 10735

    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

  • nir-616899

    SSC Rookie

    Points: 43

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply