Can you map users/logins?

Is there a way I can make deployments easier where we have different accounts in Dev & Prod that serve the same purpose? We use seperate accounts so that prod data is inaccessable to devs, and ensures connections always go to when you think they are going, e.g., for SSAS we may have:

Dom\SSAS_Dev
Dom\SSAS_Prod

I'd like an in-GUI way of saying "where you see dom\SSAS_Dev in the Source, consider this to be dom\SSAS_Prod at the target".

Currently we script out the changes, do a find&replace on the login and hope for the best - but am hoping there's a better way!

Answers

  • AlexYatesAlexYates Cambridge, UK Posts: 263 Rose Gold 2
    Are you using a SQL Source Control projects or a SQL Change Automation Project?

    In either case, I'd create a post-deploy script with logic along the lines of:

    IF (Env is Dev) { 
       Set up these dev users
    }

    IF (Env is prod) {
       Set up these prod users
    }

    Alternatively, for simplicity you may prefer to extract your dev/prod user security scripts into separate scripts/sprocs that are siimply referenced from your post-deploy script.

    In this way your security is version controlled in an environment specific manner.

    If using SQL Change Automation you could deternmine the environment using a SQL environment variable. If SQL Source Control you will probably either need to hardcode server name (I know, yuk) or use a config table in the DB with data specifying whether the database should be set up in the dev and/or prod security configuration (make sure security on this table is locked down in prod to ensure appropriater access controls are maintained).
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants and Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.