The Behavior

I am using Visual Studio SQL database projects to source control our azure SQL DB's. As part of the project definition, we have database roles which are managed.

When the project is applied to the target azure SQL database, for roles which grant permissions to objects such as tables and procedures the permissions are being revoked, the changes to the database objects applied then the permissions are being granted again by the project even if the object the permissions are granted to does not change.

This only affects roles where the permissions are defined on objects and not on schemas.

For example when deploying the project this role will have the permissions revoked and granted again:


GRANT INSERT ON [mySchema].[tableA] TO [myDbRole]

However if the role is defined as having permissions at the schema level rather than the object level this does not happen:


GRANT INSERT ON SCHEMA::[mySchema] TO [myDbRole]

I can't see any switches for the SQLPackage.exe that would prevent this behaviour other than excluding roles entirely from the project deployment, which is not what I would like to achieve.

The Real Problem

The reason we care is that we are releasing small incremental changes all the time to the DB's, during the period the deployments are being run the permissions being revoked causes issues as the applications that require the permissions can no longer function correctly.

  • You've described the behavior of the project deployment, but not a problem with it.
    – HardCode
    Mar 22 at 17:13
  • Could you configure an audit on SQL Server to capture object and permission changes so that you can see what's going on each time you apply the project changes?
    – Ronaldo
    Mar 23 at 11:29
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Mar 23 at 12:18
  • @HardCode sorry I wasn't explicit enough in the post, how can I prevent this behaviour? If the objects are not changing that the role has specific permissions granted to and the role is not changing, how can I prevent the granted object level permissions being revoked and then re-granted on each and every deployment?
    – rustayscot
    Mar 24 at 14:08
  • @Ronaldo, I can see exactly what is going on without needing to review the audit logs (although I can also see those statements being ran in the audit) as when SQL package is being executed I have set the switch: /DiagnosticsFile:$(System.DefaultWorkingDirectory)/output.log.
    – rustayscot
    Mar 24 at 14:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.