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:
CREATE ROLE [myDbRole]
AUTHORIZATION [dbo];
GO
GRANT INSERT ON [mySchema].[tableA] TO [myDbRole]
GO
However if the role is defined as having permissions at the schema level rather than the object level this does not happen:
CREATE ROLE [myDbRole]
AUTHORIZATION [dbo];
GO
GRANT INSERT ON SCHEMA::[mySchema] TO [myDbRole]
GO
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.
