Prevent dropping of users when publishing a DACPAC using SqlPackage.exe

Matt picture Matt · Jul 1, 2013 · Viewed 14.6k times · Source

Is there any way of preventing users being dropped when publishing a DACPAC using SqlPackage.exe, other than changing the setting below, which prevents all objects from being dropped if they're not in the DACPAC.

<DropObjectsNotInSource>True</DropObjectsNotInSource>

We deploy to a number of environments, each with different users. Current workarounds are to either:

  1. Script the users for each environment to recreate them after deploying
  2. Use /Action:Script and manually change the deployment script.

Neither of these are ideal though...

Answer

Tuukka Haapaniemi picture Tuukka Haapaniemi · May 11, 2017

Use SqlPackage.exe parameters (since February 2015 release: New Advanced Publish Options to Specify Object Types to Exclude or Not Drop):

Here's the actual parameters we use in our deployment:

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

The first line cleans all, but the next line further refines what not to drop. This combination proved the most effective with us to drop all unnecessary objects, yet retain the login mappings as they were.

Detailed documentation of all the parameters and their possible values can be found from MSDN - SqlPackage.exe