views:

455

answers:

2

Hi,

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!

+5  A: 

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
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
+1  A: 

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

marc_s
I would expect the name of the DB (mydb) to appear in there somewhere, am I missing something?
you have to be on your db, of course, e.g. "use mydb" before the check
marc_s