conditionally create user in SQL Server

Question

You can consult the two system catalogs sys.server_principals to check for server logins, or sys.database_principals in your specific database for users of your database:

use myDB
GO

if not exists(select * from sys.database_principals where name = 'foo')
  -- create your database user
   

if not exists(select * from sys.server_principals where name = 'foo')
   -- you need to create a server login first

Marc

Essentially combining David's answer and marc_s's answer, as requested by a comment from Chris.

Books Online says of sp_grantdbaccess:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE USER instead.

So to only create a user if the user doesn't already exist, I'd do something like this:

/* Make sure User is created in the appropriate database. */
USE mydb
GO

/* Users are typically mapped to logins, as OP's question implies, 
so make sure an appropriate login exists. */
IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = 'foo') BEGIN
    /* Syntax for SQL server login.  See BOL for domain logins, etc. */
    CREATE LOGIN foo 
    WITH PASSWORD = 'sufficiently complex password'
END

/* Create the user for the specified login. */
IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = 'foo') BEGIN
    CREATE USER foo FOR LOGIN foo
END

Despite being deprecated, sp_grantdbaccess does have the advantage of being able to use a parameter or local variable for the user/login name, as in David's answer. The first alternative I could think of to get something similar to work with CREATE USER was to use dynamic SQL. For example:

/* Make sure User is created in the appropriate database. */
USE mydb
GO

DECLARE @NewUserName sysname;
DECLARE @NewUsersLogin sysname;

SET @NewUserName = 'foo';
SET @NewUsersLogin = 'bar';

/* Users are typically mapped to logins, as OP's question implies, 
so make sure an appropriate login exists. */
IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = @NewUsersLogin) BEGIN
    /* Syntax for SQL server login.  See BOL for domain logins, etc. */
    DECLARE @LoginSQL as varchar(500);
    SET @LoginSQL = 'CREATE LOGIN '+ @NewUsersLogin + 
        ' WITH PASSWORD = ''sufficiently complex password''';
    EXEC (@LoginSQL);
END

/* Create the user for the specified login. */
IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = @NewUserName) BEGIN
    DECLARE @UserSQL as varchar(500);
    SET @UserSQL = 'CREATE USER ' + @NewUserName + ' FOR LOGIN ' + @NewUsersLogin;
    EXEC (@UserSQL);
END

Interestingly enough, Books Online also says that sp_grantdbaccess actually calls CREATE USER, and I noticed in my testing that if you don't explicitly assign a schema, sp_grantdbaccess will create one named after the user, while CREATE USER will use 'dbo' by default.

This is what we do...

IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @usrName )
BEGIN
  PRINT 'Granting access to the current database to login ' + @usrName + '...'
  -- Grant access to our database
  EXEC SP_GRANTDBACCESS @usrName
END ELSE BEGIN  
  PRINT 'Login ' + @usrName + ' already granted access to current database.'  
END  

USE [*Database_Name*];
GO

DECLARE @isUserExist int, @SQL NVARCHAR(max)
SELECT @isUserExist = COUNT(*) from sys.sysusers where name = *User_Name* 
--Checking for User Existence 
IF(@isUserExist = 0) 
BEGIN 
    SET @SQL = 'CREATE USER ' + QUOTENAME(*User_Name*) + ' FOR LOGIN ' + QUOTENAME(*User_Name*); 
    EXECUTE(@SQL); 
END