Visual Studio Database project: checking if SQL server login exists before creating it

Question

When I create a Visual Studio database project for SQL 2012 and synchronise it with an existing database (using Compare Schema), I also synchronise a SQL server login. Visual Studio generates the following script for the login:

CREATE LOGIN [my_user] WITH PASSWORD = 'somesecurepass'

When I try to publish the generated SQL on a server where this login exists, sqlcmd shows me an error:

The server principal my_user already exists.

When I look at the sql script generated by Visual Studio, I see that many objects are wrapped in IF EXISTS statements, but CREATE LOGIN is not wrapped!

I tried to wrap it manually in the SQL script in the project, but then the project does not build and there is an error pointing to IF:

SQL70001: This statement is not recognized in this context.

Now how do I force Visual Studio to generate the login creation script with the IF EXISTS check and also do not lose the synchronisation abilities?

Solution 1

Change the Build Action property of the script file (*.sql) to None. This solves the problem.

The build action property is accessed by right-clicking the sql file in solution explorer, then clicking properties. Alternatively, highlight the file in solution explorer and press 'F4'.

Solution 2

For anyone else who is looking for a complete answer, here is what I did:

1. In Visual Studio, right-click on your login script, or in my case the linked server script.
2. Click Properties.
3. Under Advanced -> Build Action, change it from Build to None.

4. In your PreDeployment script, add a line to run this script. For me, I moved my linked server script to be in the same folder as PreDeployment script. Then I can just do

   :r .\linkedserver.sql

5. When you deploy your decpac, your login script or linked server script will be executed successfully.

Solution 3

Not sure if I understand the question correctly, but I came here because of a similar scenario. After wasting a few hours with "if" and "try catch" here is my answer:

Looking at DacPac exclude users and logins on export or import I found there should be an option to exclude logins. I found that you can change the publish settings for a database project, but these settings are stored in the *.user file, which I normally don't put into source control. Looking further, I found command line options for sqlpackage.exe https://blogs.msdn.microsoft.com/ssdt/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop/. This seems to work quite nicely, also given that you can get sqlpackage.exe from NuGet (see https://blogs.msdn.microsoft.com/ssdt/2016/08/22/releasing-ssdt-with-visual-studio-15-preview-4-and-introducing-ssdt-msbuild-nuget-package/). So there is nothing in the way to build on a CI machine and deploy the result to a server.

Edit: This is on VS2015 with respective SSDT.

Comments

[JustAMartin]

Yes, I tried exactly that. It works fine if used in SQL management studio, but it does not work when entered in the query in Visual Studio Database project. It gives an error (IF line gets underlined red): SQL70001: This statement is not recognized in this context. Obviously, Visual Studio DB projects do not allow IFs around CREATE...

[danmanallen]

The script is correct but the compiler doesn't like the script when you try to build. Just right-click on the script file and select "Exclude From Solution". The project will build and it will find the file when it needs to reference it still. Let me know if this is not an acceptable solution.

[ab_732]

@danmanallen I don't think your solution would work when you deploy.

[BJladu4]

this is stupid answer. If we do this this file will not be executed when publishing!!!!!!!!!!!!!!!!!!!!

[Matt]

this is fantastic answer. Exactly what I was looking for!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[JohnnyFun]

@BJladu4, you can execute the file from another sql file using something like this :r .\some-sql-file-set-to-none.sql. Also, well played @Matt, haha

[Bharat]

Thanks lot Dongminator.. this is really help full, it's saved my life ..