What are the challenges you face when working across database platforms? Take the survey

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:


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!


  • Options
    AlexYatesAlexYates Posts: 264 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
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.