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

Question

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 1

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

Answer 2

I ran into the same issue and used Pre/Post deployment scripts to reinsert users, permissions, roles, etc like the suggested blog post. However this became unmaintainable in the long run (users unable to authenticate during deployment, if the deployment fails permissions are not restored, security changes require going through source control and re-deployment).

Recently, I reevaluated the problem as we were migrating our deployment platform. With the DacFx API (and bug fixes) released, I was able to extend the deployment process in SSDT by creating a DeploymentPlanModifier. They provide an example for filtering objects on creation, with simple modifications I filter any drops for permission based object types (using /p:AdditionalDeploymentContributors argument).

[ExportDeploymentPlanModifier( UserMappingFilter.PlanFiltererContributorId, "1.0.0.0" )]
public class UserMappingFilter : DeploymentPlanModifier
{
    public const string PlanFiltererContributorId = "Dac.UserMappingFilter";

    protected override void OnExecute( DeploymentPlanContributorContext context )
    {
        DeploymentStep next = context.PlanHandle.Head;
        while( next != null )
        {
            DeploymentStep current = next;
            next = current.Next;

            DropElementStep dropStep = current as DropElementStep;
            if( dropStep != null && ShouldFilter( dropStep ) )
            {
                base.Remove( context.PlanHandle, dropStep );
            }
        }
    }

    private bool ShouldFilter( DropElementStep createStep )
    {
        TSqlObject target = createStep.TargetElement;


        if( target.ObjectType.Name == "RoleMembership" || target.ObjectType.Name == "User" || target.ObjectType.Name == "Role" )
        {
            return true;
        }


        return false;
    }
}