I am trying to use a DACPAC database project in Azure Data Studio. So far, it works fine except for the "publishing" of the project to the actual DB. When I generate the delta script, I see that all permissions on all schemas are being dropped.

I know that I could exclude certain objects: SQL Server DACPAC Deployment Dropping Users/Roles/Permissions

However, I would prefer to have also the permissions of DB roles on a schema in the DB project. Here, of course, the order is important. (Create schema, create user, create DB role, add user to DB role, grant permission for DB role on schema)

How (and to which file) can I add the schema permissions to the project and how can it be ensured that the TSQL statements that are generated are executed in the correct oder?

Thx.

CodePudding user response：

At the linked QA have a look at the XML: there are separate parameters

• DropPermissionsNotInSource
• DropRoleMembersNotInSource

represented in sqlproj/publish.xml in a reversed way

    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropPermissions>True</DoNotDropPermissions>

By switching them to "do not drop" state you can avoid excluding permissions from deployment. Thus new permissions (defined in project but missing on target server) would be created but old ones (existing on target server but missing in the project) will not be dropped. Same goes for role membership.

Valid command order in publish script is guaranteed by the SSDT engine.

Still, permissions on target server can be lost if publishing requires object recreation. For example if you alter table-type then referencing procs will be dropped and recreated after type recreation.