I have a Visual Studio database project, every time I publish it to SQL Server, it removes the NT AUTHORITY\NETWORK SERVICE Login user.

After every publish I then have to go back into SQL Server Management Studio and add the user again via the User Mapping tab.

Does anyone know why this is and how I can stop it from doing this?

I currently have this in the dbo -> Security folder, but we have other project that also just have this and work fine with the NT AUTHORITY\NETWORK SERVICE.

CodePudding user response：

Managing settings like this can be difficult to figure out, set up, and get consistently correct, as there are a lot of situations and project settings to deal with. Start with the following, then poke around, do some experiments--it can be done.

When publishing a database from VS, you should get this screen. Clicking on Advanced...

Will get you this dialog (note that I am showing the SECOND tab)

Scroll down and select the "Do Not Drop Users" checkbox.

You would need to do this every time you publish the database--but fortunately you can save a publish "Profile"--back on the first screen, via the "Save Profile" buttons. We add these profiles in their own "Publish" folder in the project (tagged with Build Action = None) for ease of access and use.

The (very) irritating thing is, there are a LOT of settings and checkboxes buried in here, and a number of them impact database security and permissions settings. I'm reasonably certain this is the only one you'll need to deal with, but there are a lot of other settings and variations to consider. Whatever you hit, odds are there's a setting in here that, once you figure it out, can address it. Good luck!

CodePudding user response：

The answer to this was specific to the project and how it has been done.

I compared a post deployment script of another project and the one I was having problems with had extra bit at the top of the sql file where it was removing users based on names provide as publish parameters. This was not in the other project.

exec sp_executesql @sql
set @sql = 'DROP USER IF EXISTS [$(AdminLoginName)]'
exec sp_executesql @sql
set @sql = 'DROP USER IF EXISTS [$(QALoginName)]'
exec sp_executesql @sql

Removing this stops it from removing my said user, I don't honestly know why this would be in there if the other project works without it. I don't like database projects!, I find them clunky and hard to work with compares to a nice numbered migration like dbup or fluent migrator, but it's what this company use!

Appreciated the help from people, it sounds like there are some publish options to ignore users.