Sql-server – DACPAC and database drift: db users are always detected as drift even when nothing has changed

When I include a login and user in my SSDT DB project and try to deploy a DACPAC using the "Block publish when database has drifted from registered version" option, it always detects drift for the database user even when nothing has changed.

For example, I publish the project to a new server on which the database did not previously exist and everything works fine. The login and user are properly created, etc. Then I immediately publish the same project to the same server and the deployment is blocked because the db user has drifted. But I haven't changed anything on the SQL instance or even updated the project.

If I remove the login and user from the SSDT project, then everything works as expected, i.e. I don't get false positives from drift detection and I can update and deploy other types of objects without error.

This is the script for the login and user:

CREATE LOGIN AppAcct WITH PASSWORD = 'Password1';
GO

CREATE USER AppAcct FOR LOGIN AppAcct
    WITH DEFAULT_SCHEMA = dbo;
GO

GRANT CONNECT TO AppAcct;
GO

And this is the publish profile I'm using:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>DacpacDrift</TargetDatabaseName>
    <DeployScriptFileName>DacpacDrift.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=.\dev14;Integrated Security=True;Pooling=False</TargetConnectionString>
    <BlockWhenDriftDetected>True</BlockWhenDriftDetected>
    <RegisterDataTierApplication>True</RegisterDataTierApplication>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
  </PropertyGroup>
</Project>

And this is what the drift report looks like:

<?xml version="1.0" encoding="utf-8"?>
<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
  <Additions />
  <Removals />
  <Modifications>
    <Object Name="[AppAcct]" Parent="" Type="SqlUser" />
  </Modifications>
</DriftReport>

I'm using Visual Studio 2013 targeting SQL Server 2014 for this example, but I've also got other versions of SQL Server that have had the same issue. And I'm publishing directly from Visual Studio, if that makes a difference.

This is a contrived example I built trying to narrow down the root cause of the problem and make it easily reproducible. In practice, the SQL login will have a fixed SID and hashed password, and there'll be other users mapped to domain accounts. Ultimately, I'll want different security for different target instances, but for now I'd settle for getting the common logins and users to work as part of the project.

The real goal is getting drift detection to work correctly, since I'm in an environment where database drift is a likely possibility. I'd be OK managing permissions outside of the project (or as pre- or post-deployment scripts) but then that gets treated as drift by the DACPAC deployment as well.

I'm relatively new to Data Tier Applications, so it's quite possible I'm fundamentally misunderstanding how DACPAC deployments should be managed, but it was all working just fine for me until I tried to add users and logins to the SSDT DB project.

What am I missing?

UPDATE

I tried @dbajonm's suggestion, but that only gets me about halfway to where I want to be.

I removed the user and login from the project, proper, and manually added them to the target database on my test instance. Ultimately, I'll want to manage them in a post-deployment script, but for now I'm just trying to sort out the drift question.

I updated my publish profile so that it ignores logins and users etc. After a bit of trial and error I ended up with these new settings in my profile:

<ExcludeUsers>True</ExcludeUsers>
<ExcludeLogins>True</ExcludeLogins>
<IgnorePermissions>True</IgnorePermissions>
<IgnoreRoleMembership>True</IgnoreRoleMembership>

Now, when I try to publish, the script it generates does not drop users or their permissions or role memberships. That's good as far as it goes, since I won't break any permissions were I to actually deploy that DACPAC. The problem is that publishing is still blocked because those users that are ignored in the deployment script are still detected as database drift.

So here's the root of my question, in short:

• if I include users and logins in the project as deployable objects (i.e. script's Build Action = Build), they are always detected as drift even if they haven't changed.
• if I don't include users and logins in the project, then I have to manually deploy them (or use a post-deploy script), in which case they are always detected as database drift.

It seems like no matter what I do, deployment will always be blocked due to database drift. Can this possibly be right? Is there really no way to reliably detect drift for a database that includes database users (i.e. pretty much all of them)?

I know I can ignore drift and publish anyway, but that makes drift detection more-or-less useless for my purposes. I'm trying to lead our group away from developing directly against the database, and towards treating database code as a first class citizen worthy of version control, etc. During the transition, we're going to have people making db changes outside of SSDT projects, which is why drift detection is such big deal to me. If I'm always getting false positives from drift detection, then it becomes more of a hassle than it's worth.