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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
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.
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?
If grantees are already in the project then yes.
upd are you saying you don't have sources (sqlproj, *.sql files), just a dacpac (single binary file)?
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.