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:

  
    True
    DacpacDrift
    DacpacDrift.sql
    Data Source=.\dev14;Integrated Security=True;Pooling=False
    True
    True
    1
    True
    True
    True
  

And this is what the drift report looks like:

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

To handle the users and logins external from your DACPAC, set the following option. This can be within the publish config file or your projects settings (so both your scenarios).

From VS: project properties > Debug > Advanced... > Ignore > Excluded Object Types. Then check "Exclude logins", "Exclude users", database roles, etc. Whatever you want to be excluded when the publish/deploy does its comparison.

From your publish config file, use the following

True
True


This exclusion option is buried inside the settings. However, this option was not even available until 2014 and caused much gnashing of teeth for on-prem sql databases! At least we have it now!

Keeping current with SSDT in Visual Studio and on your build server is imperative!

StackExchange Database Administrators Q#149569, answer score: 2