Sql-server – Ignore login mappings during Dacpac compare

sql-server-2012ssdt

We're trying to automate the deployment of databases through development, testing and production. Our approach, using SSDT, is to compare DACPACs from the different environment to generate update scripts, and as a final step, map the correct windows logins to the correct users.

/p:IgnoreUserLoginMappings

Looking at the documentation for SqlPackage.exe there is a property IgnoreUserLoginMappings that seems to imply that it would ignore mappings, so that they are not included in the dacpac and hence not updated during the comparison and script generation.
But, when we use this property during our extract the users generated are WITHOUT LOGIN, like this in the dacpac:

<Element Type="SqlUser" Name="[Domain\Account]">
  <Property Name="IsWithoutLogin" Value="True" />
    <Relationship Name="DefaultSchema">
      <Entry>
        <Annotation Type="PersistedResolvableAnnotation" Name="[Domain\Account]">
                <Property Name="TargetTypeStorage" Value="SqlSchema" />
        </Annotation>
         </Entry>
    </Relationship>
</Element>

which is NOT what we want! The subsequent generated update looks something like this:

CREATE USER [Domain\Account] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Domain\Account];

Are we doing something wrong or is this a bug, I've searched and can't see much info on this property, is anyone using it that can help, or is there another approach?

Related question: here

Best Answer

I found using SqlPackage.exe will not drop security objects.

I was having the same issue as well. Going forward, we are going to deploy database changes using DACPACs. We need a way to exclude users, roles and logins from the update process so they are not dropped from the target SQL Server.

Our database is installed at hundreds of clients sites and many SQL Server installations have had their security setup by the DBAs at that company. They will often add their own domain logins and users and in some cases tighten down roles more than our defaults.

I noticed that when choosing the option to "Upgrade Data-Tier Application" within SQL Server Management Studio 2012 I did not have the option to ignore security objects. The review actually stated it was going to drop users and roles.

Screen Shot of Upgrade DTA review

But when I used SqlPackage.exe it did not drop the security objects. This is because the parameter "DropObjectsNotInSource" is set to false by default.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:.\bin\debug\SandBox.dacpac /TargetServerName:sandboxserver /TargetDatabaseName:SandBox

The only downside is that if you want to drop deprecated objects from the database, you will need to add the drops to the post deployment script.

I hope Microsoft will add this feature in the future. These objects are often managed by DBAs and not developers.

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/775839/ssdt-add-publication-setting-to-ignore-database-users

Related Question