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

Jim Nguyen 61 Reputation points
2021-05-20T20:33:52.51+00:00

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>"

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,318 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. AmeliaGu-MSFT 13,956 Reputation points Microsoft Vendor
    2021-05-21T09:13:03.597+00:00

    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.


  2. Tom Phillips 17,711 Reputation points
    2021-05-24T16:43:09.757+00:00

    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