41

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • possible duplicate of [Checking if a SQL Server login already exists](http://stackoverflow.com/questions/1379437/checking-if-a-sql-server-login-already-exists) – bummi Dec 02 '14 at 12:52

6 Answers6

55

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

Mark Iannucci
  • 135
  • 3
  • 11
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I would expect the name of the DB (mydb) to appear in there somewhere, am I missing something? –  Apr 22 '09 at 18:31
  • 1
    you have to be on your db, of course, e.g. "use mydb" before the check – marc_s Apr 22 '09 at 18:46
28

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.

Community
  • 1
  • 1
Matt
  • 5,052
  • 4
  • 36
  • 54
16

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
David
  • 34,223
  • 3
  • 62
  • 80
  • 1
    for new development on SQL Server 2005 I'd use sys.database_principals instead of sysusers and stick with CREATE USER instead of sp_grantdbaccess. – Matt Apr 22 '09 at 18:41
  • A bit old, but it should be `sys.server_principals` and not `sys.database_principals` for this case. – codenamezero Sep 30 '16 at 13:08
1
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
Amol Bavannavar
  • 2,062
  • 2
  • 15
  • 36
0

I asked exactly the same question regarding the SQL user already existing when the script runs - the question, and some very useful answers are here:

Checking if a SQL Server login already exists

Hope this helps.

Community
  • 1
  • 1
Brett Rigby
  • 6,101
  • 10
  • 46
  • 76
-3
USE MyDB
GO

BEGIN TRY
  CREATE USER [foo] FOR LOGIN [foo]
END TRY
BEGIN CATCH
 -- User exists - do nothing
END CATCH
bummi
  • 27,123
  • 14
  • 62
  • 101
Badgesa
  • 3
  • 1
  • 1
    As long as the only potential error is that the user already can login. – Karlth Jul 14 '16 at 13:41
  • 1
    Downvoted because if any other error is raised, like "Password Policy" or "Invalid Login", these errors will be swallowed by the Try/Catch – Raffaeu Apr 12 '17 at 09:39