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

Celandine

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

Gasometry

One might also need to exclude: Credentials;DatabaseScopedCredentials – Zeller

I thought that based on making these changes in the project file (actually it gets saved to the .sqlproj.user file) it'd get compiled into the dacpac. Is that not the case? The only way is to pass as SqlPackage.exe parameters? – Pathoneurosis

Nevemind, I didn't know you could use a publish profile instead of parameters. – Pathoneurosis

Note that if you're running this in powershell, the list of object types need to be in quotes: /p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions" – Hibernate

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;
    }
}

Daisydaitzman

could you please explain how to do that? I couldn't find how to install/setup/whatever this. I found something that I need to create dll for that to pass it as /p:AdditionalDeploymentContributors argument, however I couldn't find how to create this dll – Moltke

Hey sidux - you can either use this one that is pre-made for you or use it as the basis to write your own: agilesqlclub.codeplex.com (I wrote it) - more details the.agilesql.club/Blogs/Ed-Elliott/… – Mudlark

How to use your own one? I am communicating with Ed (author of agilesqlclub already) and we can not get it working yet. I am working with VS2012 SSDT. The main problem is that I have no idea where the SqlPackage.exe located that is used by VS2012. I tried search, many other things and still no effects – Moltke

Answer 3

We handle this in post-deploy scripts. It's a bit harder to set up, but once set up allows you to configure a slightly different script for each environment. We use this in conjunction with Publish Profiles with a different profile per environment. Basically, you use Powershell to generate a bunch of scripts for users and permissions, add those scripts to your project(s), and then Include the files in the project. Add what is referred to in the blog post as "SecurityAdditionsWrapper.sql" to your post-deploy script, and you should be good. Just remove the other security from your project to ensure that it's set correctly.

http://schottsql.blogspot.com/2013/05/ssdt-setting-different-permissions-per.html

There are also options in SSDT for: "Drop Permissions not in source" - False "Drop role members not defined in source" - False "Ignore permissions" - True "Ignore role membership" - True

We use those, but if you need better control over your users/permissions by environment, I'd strongly recommend checking out that blog post. (With thanks to Jamie Thomson for the original idea.)

Aldin

There is an MS Connect issue relating to this here: connect.microsoft.com/SQLServer/feedback/details/775839/… Although, MS marked it as "Won't Fix" in early 2013. – Baluchistan