Visual Studio数据库项目:在创建SQL Server登录名之前检查它是否存在

Visual Studio数据库项目:在创建SQL Server登录名之前检查它是否存在

问题描述:

当我为SQL 2012创建Visual Studio数据库项目并将其与现有数据库同步(使用比较架构")时,我还将同步SQL Server登录名. Visual Studio为登录生成以下脚本:

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'

当我尝试在存在此登录名的服务器上发布生成的SQL时,sqlcmd向我显示错误:

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.

当我查看由Visual Studio生成的sql脚本时,我看到许多对象都包装在IF EXISTS语句中,但是CREATE LOGIN却没有包装!

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!

我尝试将其手动包装在项目中的SQL脚本中,但随后该项目无法构建,并且出现了指向IF的错误:

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.

现在,如何强制Visual Studio使用IF EXISTS检查生成登录创建脚本,同时又不失去同步能力?

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?

将脚本文件(* .sql)的生成操作"属性更改为无".这样就解决了问题.

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

通过在解决方案资源管理器中右键单击sql文件,然后单击属性来访问生成操作属性.或者,在解决方案资源管理器中突出显示该文件,然后按"F4".

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