2

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.

thomasjbarrett
  • 155
  • 1
  • 1
  • 9

3 Answers3

2

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.

Peter Schott
  • 4,521
  • 21
  • 30
  • Thanks, I like the idea of using roles. Seems similar to the domain approach of user groups instead of users. It would take a fair bit of work to define those, since none exist right now, but it seems less complicated than scripts or separate projects. – thomasjbarrett Oct 02 '20 at 10:34
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

Cece Dong - MSFT
  • 29,631
  • 1
  • 24
  • 39
  • Thanks, that's good information about the deployment process, but I was looking specifically for how to manage object and database permissions inside the project. – thomasjbarrett Oct 02 '20 at 09:42
  • It seems your query is not related to DevOps, but more related to database project. Let's wait database experts to help us solve your issue. – Cece Dong - MSFT Oct 06 '20 at 03:33
1

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.

Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
  • I saw the separate projects idea, that sounds interesting. From a deployment perspective, if I understand it, instead of 1 pipeline that goes from dev to QA to prod, you would have 3 pipelines for each environment? – thomasjbarrett Oct 02 '20 at 10:31
  • 1
    You can still have single pipeline if you use project name as a variable. Just pass different project names in your pipeline for different environment. – Dmitrij Kultasev Oct 02 '20 at 11:51