SSDT db-project compare throws "source schema drift detected" for members of sysadmin

Question

We are using SSDT database projects where changes will be done in the dev-DB, then a compare is done between the devDB and the VS-DBproject and selected changes is updated into the VS-project. Lately (maybe for about a month) the update of the VS-project has started to fail for me with the message "Source schema drift detected. Press Compare to refresh the comparison". There is no changes going on in the database at the time, of that I am sure (tried against several databases, also very small ones that no one else is using).

There are a couple of strange things that I cannot explain:

• We have three environments, dev, test and prod. The problem only happens when going against the dev or test-environments. Against prod it works. All three servers have the same version of SQL Server and Windows Server.
• We are several developers, but the problem only occur for some. After further investigation, it seems that those who get the problem are those that have sysadmin permissions on the SQL Server.

There is a workaround that is working for me: I can create a DacPac of the source DB and do the compare against that, and then the update will go fine. But this is more cumbersome to do. It is OK as long as it is only the sysadmins with the problem, but if all developers should get this problem...the workaround would be a problem.

Any one else have seen this problem? Any suggestions?

Our environment: All developers use software installed on a Windows Server 2019: Visual Studio Professional 2019 version 16.11.16 SSDT 16.0.62205.05200

SQL Server is running on a Windows Server 2016. SQL SErver version is: 15.0.4312.2

Answer 1

The only work-around that I have seen that shows any promise is related to folder structure of the project itself. The error has nothing to do with the state of the actual database. It isn't because you've got too many developers working against a dev DB and the schema has literally drifted. You need to have your project setup so that the solution file and the sqlproj file are in the same directory, which is the non-default setup for a database project. If you want multiple database projects in the same solution (or repository), you are out of luck. Time to break them into separate repositories, so there's only one sqlproj file per solution file.