SQLPACKAGE.EXE - Not Ignoring Users Or Logins for Database Permissions

Question

The deployment profile has been configured to exclude logins and users, but it looks like the change script being generated still includes logins and users for database level permissions like CONNECT or VIEW DEFINITION. The database projects has recently been updated to include permissions for various stored procedures, tables, views, and similar objects associated with database roles, so permissions are not ignored as part of the deployment profile.

I assumed that since users and logins were excluded as part of the deployment profile, that it would be excluded as part of the change script. That does not seem to be the case. It appears that if you include permissions as part of the deployment profile, it takes precedence over the exclusion of users and logins.

This scenario presented itself when I extracted a dacpac from one of our databases where the property was set to include permissions. When generating a change script from that dacpac with a different database target, the change script attempted to add users that do not exist for that instance.

Examples of the scripts used:
sqlpackage.exe /Action:Extract /p:IgnorePermissions=false /SourceConnectionString:"<My Source Connection String>" /TargetFile:"<My Source DACPAC>"

sqlpackage.exe /Action:script /Profile:"<My Deployment File>" /SourceFile:"<My Source DACPAC>" /TargetConnectionString:"<My Target Connection String" /OutputPath:"<My Change Script>"

Answer 1

Hi J-I-M-B-O,
Could you please share us the scripts? What action is performed by the SQLPackage?
Please refer to the following articles which might help:
Using Publish Profiles to Deploy a DACPAC Without User Accounts
Prevent dropping of users when publishing a DACPAC using SqlPackage.exe
sqlpackage removes CONNECT permission when "Do not drop users" is true

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You need to use /p:IgnorePermissions=true when you run sqlpackage.exe.

You also likely need to use: IgnoreUserSettingsObjects=true and ExcludeObjectTypes=Users,ServerRoleMembership,ServerRoles,DatabaseRoles

There are many command line options to use when doing this via the command line.

See:
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-script?view=sql-server-ver15