Advance Deployment Settings: Drop, Ignore & Exclude on USER not working

1
votes

We are using SSDT project in visual studio to manage my SqlServer deployments. We create dacpac file and send it to our clients.
Then they update their DB using "Sql Server > Task > Upgrade Data-tier Application" option.

While doing this, we found it drop all the User which is not in dacpac but present in SqlServer DB. (though users are not droping - if we use SqlPackage.exe command line tool)

To prevent this DROP, we implement "Drop, Ignore & Exclude" setting in Advance Deployment Settings in SSDT project of Visual Studio. But this seems not working.

Ignore & Exclude settings Drop settings

Is I missing something?
How do i prevent USER from Drop?

SQL Server Version:
Microsoft SQL Server Management Studio 12.0.4213.0
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.9600

2
sql-servervisual-studioconfigurationsql-server-data-toolsdacpac

2 Answers

0
votes

Your best bet might be to package all of this up for the users with a Publish Profile set up to not drop users as well as a way they could easily use SQLPackage to do the changes. I know there's a NuGet package containing the DacFX now and Ed Elliott wrote up some stuff about using it in PowerShell here: https://the.agilesql.club/blogs/Ed-Elliott/DacFxed-Nugetized-DacFx-Powershell-Wrapper (see Solution 3)

0
votes

If the customer is using an older version of DacFX, the "Ignore Users" etc. settings will be silently ignored.

Since you presumably don't control this, it might be an idea to package a current version along with your dacpac, and provide the customer with a deployment script.