14

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?

JustAMartin
  • 13,165
  • 18
  • 99
  • 183
  • what about removing the user into the pre-deployment script? just a guess... – ab_732 Jun 05 '12 at 19:26
  • @abx78: yes, I thought about it, but then I'll have to move also the actual database user creation for that login to the pre-deployment (if I don't, Visual Studio yells at me that my_db_user has a bad reference to that my_user login). An again - if after deploying the proyect to the database, someone runs Schema Compare, those logins and users will reappear in the project as they initially were. – JustAMartin Jun 06 '12 at 09:35
  • I don't understand why the schema compare finds the login if it already exists in the target. – Nick.Mc Feb 07 '17 at 22:13
  • @Nick.McDermaid - the problem is that it does not find the login, because Visual Studio generates deployment script which does not even check IF LOGIN EXISTS; it just tries to create it. Therefore I needed some workaround. – JustAMartin Feb 08 '17 at 08:59

5 Answers5

22

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'.

angus
  • 690
  • 10
  • 26
Jai
  • 336
  • 2
  • 4
  • 2
    this is stupid answer. If we do this this file will not be executed when publishing!!!!!!!!!!!!!!!!!!!! – BJladu4 Nov 18 '15 at 22:26
  • 2
    this is fantastic answer. Exactly what I was looking for!!!!!!!!!!!!!!!!!!!!!!!!!!!!! – Matt Feb 25 '16 at 16:28
  • 4
    @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 – JohnnyFun Aug 17 '16 at 15:31
5

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.
Dongminator
  • 795
  • 10
  • 15
1

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.

csv
  • 23
  • 6
0

I have exactly the same problem and the solution from me was to have a pre-deployment script deleting users:

IF EXISTS(SELECT TOP 1 1 FROM master.sys.server_principals WHERE name = 'my_user') BEGIN
   DROP LOGIN [my_user]
END

The problem is that maybe you have changed the password, permissions, or settings of the LOGIN [my_user] since it was created, and after droping and creating it, you will lost that customized settings.

Alex
  • 797
  • 10
  • 30
-1
IF NOT EXISTS
(
SELECT [Column Name Where Username Is Stored] 
FROM [Table Name That Holds The Login's] 
WHERE name = 'my_user'
)
BEGIN 
CREATE LOGIN [my_user] WITH PASSWORD = 'somesecurepass'

ELSE....

And then you can write an else statement to handle it however you want whether that be updating the password to the new one or simply returning a message saying that the user already exists. Let me know if you need help writing that part.

danmanallen
  • 175
  • 3
  • 14
  • 1
    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... – JustAMartin Jun 04 '12 at 22:08
  • 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. – danmanallen Jun 05 '12 at 16:27
  • @danmanallen I don't think your solution would work when you deploy. – ab_732 Jun 05 '12 at 18:44