2

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.

Chris
  • 143
  • 7

1 Answers1

1

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.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
  • ok thanks. And is there also a way to manage all permissions in the project? Excluding them seems to be a valid workaround but would is be also possible to have all permissions in the project? Then, missing permissions can be created in the DB and permissions that are not in the source can be dropped. – Chris Mar 11 '22 at 07:18
  • It is possible. I don't really understand what you're asking since as you stated you know how to have schema grants in the project. Other permissions can be defined similarly. – Ivan Starostin Mar 11 '22 at 09:26
  • Ok, so let me rephrase it a bit. I generated the DACPAC based on an existing DB. It generated everything except the schema permissions. So I understand based on your answer that I just need to add a new file to the project containing the schema permissions and that should be all, correct? – Chris Mar 12 '22 at 10:02
  • If grantees are already in the project then yes. – Ivan Starostin Mar 12 '22 at 12:21
  • upd are you saying you don't have sources (sqlproj, *.sql files), just a dacpac (single binary file)? – Ivan Starostin Mar 12 '22 at 12:23
  • no, I also have the sources not just the binary. – Chris Mar 13 '22 at 13:02