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?
There are some dmvs where you can get an idea about if it is in use: SELECT * FROM sys. syslogins contains the datetime when the account was last updated. There is also a column which tells you when the login was last accessed. Bu the last accessed column is "Identified for informational purposes only.
SQL Server: Find Logins in SQL Server Answer: In SQL Server, there is a catalog view (ie: system view) called sys. sql_logins. You can run a query against this system view that returns all of the Logins that have been created in SQL Server as well as information about these Logins.
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'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With