Can SSDT maintain different environment users and permissions in a single project?

Question

We use different service accounts for the different deployment environments, so Dev has Account_A and Prod has Account_B, and any test app using Account_A will not have access to Prod. Or, as another example, Account_A can have read/write permissions in Dev, but only read permissions in Prod.

Up until now there has been no source control on the database definitions, just manual scripts everywhere, and I'd like to create a SSDT solution in Azure DevOps for this. I understand how you can set up releases to handle different database names across environments (Db_Dev vs Db_Prod, for example), but I'm not able to find anything about different users & permissions across environments.

Is this possible in SSDT? As far as I can tell, I have 2 options, but I'm hoping there's a better way:

1. Handle users and permissions outside of source control
2. Handle them somehow in a post-deployment script.

Caveat: I'm only talking about Windows Authentication users & groups. Passwords will obviously not be going into source control.

Answer 1

On DevOps side, an environment is a collection of resources, such as Kubernetes clusters and virtual machines, that can be targeted by deployments from a pipeline. Typical examples of environment names are Dev, Test, QA, Staging, and Production. You can secure environments by specifying which users and pipelines are allowed to target an environment.

You can control who can create, view, use, and manage the environments with user permissions. There are four roles - Creator (scope: all environments), Reader, User, and Administrator. In the specific environment's user permissions panel, you can set the permissions that are inherited and you can override the roles for each environment.

More details, check the following link:

https://learn.microsoft.com/en-us/azure/devops/pipelines/process/environments?view=azure-devops#security

Answer 2

Managing users and permissions in SSDT is a bit pain in the and usually they are not maintained there. However you still have options:

• Create post-script as you mentioned (bad choice)
• Create separate projects for each environment

What I mean by separate project is: you need to create separate project for each environment, then add reference of your main project (where all of your objects exist) and set reference type as "the same database". Then in that project you'll add all needed users/permissions/modifications. These projects will have their own publish profiles as well. 1 issue you might face as well is that his project should reference ALL databases/dacpacs as your main project as well.

Answer 3

I wrote about this a long time ago here: https://schottsql.com/2013/05/14/ssdt-setting-different-permissions-per-environment/

You really are dealing with environment variables and a bunch of post-deploy scripts in order to do this. Your better option is to assign permissions to database roles so those are all consistent, then assign your users to those roles in each environment as appropriate - outside of SSDT. It's a lot less painful than trying to create/maintain logins and users in a series of post-deploy scripts in the long run.