Check If A Login Exists In SQL Server 2008
If you are creating a SQL Server login using a script then it is advisable to check if the login you are creating already exists. If you do not do this then SQL Server will throw an error. For example if I execute this statement when a login with name test_user already exists, I will get the following error.
CREATE LOGIN test_user WITH PASSWORD = 'Password123'
Msg 15025, Level 16, State 1, Line 1 The server principal 'test_user' already exists.
To avoid this a check can be used before creating the login. One way to do this is to write a statement like this.
IF NOT EXISTS(SELECT name FROM master.dbo.syslogins WHERE name = 'test_user') BEGIN CREATE LOGIN test_user WITH PASSWORD = 'Password123' END
The above statement will check for the login and it will only create it if the login does not already exist. This works but it is not the best way to do it in SQL Server 2008. The problem is that we are querying master.dbo.syslogins which is available only for backward compatibility and as per Microsoft it will be removed in future versions. So the best way to do a check before creating a login is to use sys.sql_logins or sys.server_principals system view. Our statement can be re-written in following two ways.
IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name = 'test_user') BEGIN CREATE LOGIN test_user WITH PASSWORD = 'Password123' END
OR
IF NOT EXISTS(SELECT name FROM sys.sql_logins WHERE name = 'test_user') BEGIN CREATE LOGIN test_user WITH PASSWORD = 'Password123' END
This approach will ensure that your script will work in future versions of SQL Server when Microsoft decides to remove master.dbo.syslogins.
2 Responses to Check If A Login Exists In SQL Server 2008
Leave a Reply Cancel reply
Top Posts
- LINQ To SQL Tutorial
- LINQ To SQL Join On Multiple Conditions
- Code Sample: Programmatically Download File Using C#
- Windows 7 Control Panel In Classic Mode
- More Details Emerge On Microsoft Master Certification
- Use SqlConnection With LINQ To SQL
- Free Icons And Images With Visual Studio 2008
- Capture XML In WCF Service
- Dynamic Sort With LINQ
- StyleCop Tutorial
Tags
.Net 2010 ADO.NET ASP.NET Azure Blogging Books Browsers C# Certification Cloud Computing Code Snippets Community Data Services Eclipse Entity Framework Google IDE Java LINQ Mac Microsoft Museum NetBeans Office Oracle REST SharePoint Silverlight SQL Server T-SQL Tips Tools Training Visual Studio Visual Studio 2010 WCF Web Windows Windows 7 Windows Forms Windows Live WMI WPF XAML


This is great!!!! but…..
What if you wanted to continue with another statement if the user existed.
IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name = ‘test_user’)
BEGIN
CREATE LOGIN test_user WITH PASSWORD = ‘Password123′
END
If it does exist how can we carry on executing other statements or can we just exit.
I’m doing this within my Delphi application and it crashes.
I run 3 procedures from the one button click and the ckeck for user is the first. If user exists it halts the program.
Is there not and IF THEN ELSE in SQL?
Thanks. very useful article.