conditionally create user in SQL Server

I would like to create a user 'foo' in database 'mydb' if the user doesn't already exist. Currently my script looks like this:

USE [mydb]
CREATE USER [foo] FOR LOGIN [foo]
GO

However if the user already exists, this fails with an error message:

Msg 15023, Level 16, State 1, Line 2
User, group, or role 'jsrvpp' already exists in the current database.

How can I change the script so that the user is only created if they doesn't already exist?

Thanks!

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.