SSDT Warning SQL72038: The object already exists in database with a different definition and will not be altered.

If you get the SQL72038 warning when you deploy your database projects, it will not break your deployments, but it can be annoying. I’d recently moved deployments to TeamCity where messages like this are flagged up by colour coding the output in the build log. I really wanted to get it resolved.

Build succeeded.

"C:\TeamCity\buildAgent\work\c5dac4e04d41d88a\MyDBProject\MyDBProject.sqlproj" (default target) (1) ->
(SqlPublish target) ->
  C:\TeamCity\buildAgent\work\c5dac4e04d41d88a\MyDBProject\bin\Debug\MyDBProject.publish.sql : Deploy warning SQL72038: The object [UserOne] already exists in database with a different definition and will not be altered.

    1 Warning(s)
    0 Error(s)

In my case the object in question was a login/user, named UserOne. The script in SSDT looked like

create login [UserOne] with password = N'NotTheRealPassword';
go
create user [UserOne] for login [UserOne];
go

alter role [db_datareader] add member [UserOne];
go
grant execute on schema::[dbo] to [UserOne]
go

This is a simple script that adds the user to the database. You need to include a CREATE LOGIN so that the model has something to attach the user to, but in reality you would have created that at the server level with a better password. I add the user to the database role db_datareader and give permissions to execute stored procedures in the dbo schema.

The login, user account and permissions already existed on the target server as this project has been deployed many times. But each deployment was still throwing up the SQL72038 warning. It was not straightforward or easy to spot what was different between the SSDT model and the already existing object. I thought running a schema compare would shed light, but still nothing.

LOGIN vs USER

Much of my confusion was in the fact that the warning message didn’t explicitly say whether the object [UserOne] was referring to the login or the user. Given that database projects are, by their nature, concerned with databases, I tended to lean towards this being the database user. But no.

I resorted to scripting out the login in SSMS, and slowly everything became clear.

use [master]
go

/* For security reasons the login is created disabled and with a random password. / /
* Object: Login [UserOne] Script Date: 30/08/2024 14:17:04 /
create login [UserOne] with password=N'Xz1C4yEYXSUUbwd2/R50YdFh7o5DLWZptu5Qz3ldcQ0=', default_database=[MyDB], default_language=[us_english], check_expiration=off, check_policy=off
go
alter login [UserOne] disable
go

The existing login had a value for the default database, whereas the SSDT model login did not specify anything, so the default of master is used. Also the default for check_policy is on. This is different from what is implied in the model. I decided the default database should be master, so changed that on the server itself. I also added the check_policy option to the script in SSDT.

In some cases I also needed to add an explicit GRANT CONNECT. The final script looking like this

create login [UserOne] with password = N'NotTheRealPassword', check_policy = off;
go
create user [UserOne] for login [UserOne];
go
grant connect to [ReportUK];
go
alter role [db_datareader] add member [ReportUK];
go
grant execute on schema::[dbo] to [ReportUK]
go

And that made all the warnings go away. Happy days.