DACPAC drops existing users and permissions from database when upgrading through SSMS

Question

I'm developing my database through a database project in visual studio, and whenever I need to upgrade the schema of my database I like to use SSMS to do this using a DACPAC.

But whenever I use my DACPAC to upgrade the schema of my database, then the current existing Security Users and permissions for these users are being dropped. And I need them to remain in the database.

Is there anyway to configure the DACPAC to not drop users and their permissions whenever I upgrade my database using "Upgrade Data-tier Application" in SSMS?

Thanks in advance.

Answer 1

I usually prefer to exclude security items in this way:

/p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions

However in that case, you have to set a few script in the post-deploy scripts to handle those items.

Answer 2

As far as I know, there isn't a way to skip that step when using the "Upgrade Data-tier Application" wizard in SSMS.

Your options are:

• Include the required security objects (CREATE USER statements, etc.) in the dacpac
  • This may or may not work for your situation, depending on whether you have different required security objects in different target environments
• Use sqlpackage.exe to publish the dacpac rather than using the SSMS wizard, and pass the ExcludeObjectTypes parameter, listing out all of the security objects you want to skip. See here for examples, and what objects are available to be passed in: SqlPackage Publish parameters, properties, and SQLCMD variables

Here's an example of the second approach:

sqlpackage 
    /Action:Publish \
    /SourceFile:"C:\YourDatabaseName.dacpac" \
    /TargetConnectionString:"Your connection string" \
    /p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions"