How to handle environment specific security permissions when automating Azure SQL database deployment?

Question

We are trying to integrate Azure SQL database deployment into our CICD process.

We have three environment development, staging, and production.

CI pipeline will produce DACPAC file. CD pipeline will deploy DACPAC into the next environment. However, it's failing because of conflicts of roles and permissions that are assigned to the databases in each environment.

What's the best way to handle this situation?

Answer 1

I think you should use an exclude parameter while generating your dacpac or when pusblishing.

while extracting add parameter (Doc sqlpackage extract):

sqlpackage /Action:extract /p:ignorePermissions=true

while publishing use (Doc sqlpackage publish):

sqlpackage /Action:Publish /p:ExcludeObjectTypes="Users;Permissions"

For me, I would choose the second option with publish since it gives more control over objects you want to exclude (in future you might want to exclude something else).