Options
Can you map users/logins?
JJ_DBA
Posts: 1 New member
in SQL Compare
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!
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!
Tagged:
Answers
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).
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn