Checking if a SQL Server user already exists

Question

I need to check if a specific user already exists on the SQL Server, and if it doesn't, then I need to add it.

I have found the following code to actually add the user to the database, but I want to wrap this in an IF statement (somehow) to check if the user exists first.

CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
GO

I understand that I need to interrogate a system database, but not sure where to start!

Answer 1

From here

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

EXEC sp_executesql @SqlStatement
End

Answer 2

This works on SQL Server 2000.

use master
select count(*) From sysxlogins WHERE NAME = 'myUsername'

on SQL 2005, change the 2nd line to

select count(*) From syslogins WHERE NAME = 'myUsername'

I'm not sure about SQL 2008, but I'm guessing that it will be the same as SQL 2005 and if not, this should give you an idea of where t start looking.

Answer 3

Try this (replace 'user' with the actual login name):

IF NOT EXISTS(
SELECT name 
FROM [master].[sys].[syslogins]
WHERE NAME = 'user')

BEGIN 
 --create login here
END

Answer 4

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.

Answer 5

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 = 'TConnect.User')
BEGIN
    CREATE LOGIN [TConnect.User] 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