Checking if a SQL Server login already exists

Question

Here's a way to do this in SQL Server 2005 and later without using the deprecated syslogins view:

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'LoginName')
BEGIN
    CREATE LOGIN [LoginName] WITH PASSWORD = N'password'
END

The server_principals view is used instead of sql_logins because the latter doesn't list Windows logins.

If you need to check for the existence of a user in a particular database before creating them, then you can do this:

USE your_db_name

IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'Bob')
BEGIN
    CREATE USER [Bob] FOR LOGIN [Bob] 
END

From here

If not Exists (select loginname from master.dbo.syslogins 
    where name = @loginName and dbname = 'PUBS')
Begin
    Select @SqlStatement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + ' 
    FROM WINDOWS WITH DEFAULT_DATABASE=[PUBS], DEFAULT_LANGUAGE=[us_english]')

    EXEC sp_executesql @SqlStatement
End

As a minor addition to this thread, in general you want to avoid using the views that begin with sys.sys* as Microsoft is only including them for backwards compatibility. For your code, you should probably use sys.server_principals. This is assuming you are using SQL 2005 or greater.

You can use the built-in function:

SUSER_ID ( [ 'myUsername' ] )

via

IF [value] IS NULL [statement]

like:

IF SUSER_ID (N'myUsername') IS NULL
CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
GO

https://technet.microsoft.com/en-us/library/ms176042(v=sql.110).aspx