5

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.

  • When deploying a new database user, the password is always reset and it could be something like this. – Randolph West Sep 15 '16 at 1:37
  • When you do a schema compare and visually check the logins (drill into the properties folder ), is this happening because the types are different between the database and project? I have an issue that logins are always detected as different because the type property is different. However this property is never applied to the project. – Nick.McDermaid Apr 9 '18 at 2:30
1

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

    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeLogins>True</ExcludeLogins>

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!

  • This seems promising, but it's not actually making any difference for me. I've removed users & logins from the project, but they're still being detected as drift when I try to deploy. And since they're not part of the project, they are actually drift, but I thought that's what the ExcludeUsers and ExcludeLogins was supposed to handle. – Matt Oct 6 '16 at 16:44
1

In our processes using Database projects and DacPacs, we decided against using DacPacs to deploy anything except the basic components that are changed as part of ongoing maintenance and support. This is just tables, views, stored procedures, extended properties, user defined data and table types, Scalar and table Valued Functions.

We instead manage security and database settings via a collaborative approach including the development teams and the database team.

Of course we are not in a situation where we are creating databases from scratch very often and security is pretty stable requiring infrequent changes.But the security is included in the project. To fix the problems we originally had with the logins causing build failures, we changed the Create User objects to eliminate the password:

Create User [user] Without Login;

We have not had problems with drift being detected on these user objects. You could try this aproach to see if it fixes your problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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