3

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

3
  • So what do you want? You haven't said that anywhere. From the documentation ("Specifies whether relationships between users and logins will be ignored."), it appears to work as expected (at least to me).
    – Jon Seigel
    Jul 18, 2013 at 12:58
  • But a user mapped to a Windows login is different from a WITHOUT LOGIN user, we ultimately want to ignore the login mapping when doing a comparison. but that's not what's happening! We can't 're-map' these WITHOUT LOGIN users back to their correct logins, as a post deployment step for instance.
    – Dog Ears
    Jul 18, 2013 at 13:43
  • I am 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 user and in some cases tighten down roles more than our defaults.
    – sean
    Sep 5, 2013 at 15:09

1 Answer 1

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.