Create database user conditionally syntax error

Question

I'm using the following to create a user if it doesn't already exist in the database:

use DBExample
GO

IF NOT EXISTS (SELECT * from sys.database_role_members WHERE USER_NAME(member_principal_id) = 'user1')
BEGIN
CREATE USER [user1] WITH PASSWORD = 'abc')
END

EXEC sp_addrolemember 'role1', 'user1'
GO

DBExample already has a user1, so when I try to run the script, SQL Server Management Studio complains about an 'Incorrect syntax near 'user1'. (in the create user line)

What am I missing to make this work?

Ian Preston · Accepted Answer · 2013-02-04 17:54:48Z

2

I think you're confusing Logins with Users - in SQL Server 2008R2, at least, you can't have one without the other. I'd recommend having a quick look at Books Online for these concepts.

You're probably looking for something like:

IF NOT EXISTS (SELECT * from sys.server_principals WHERE name = 'user1')
BEGIN
CREATE LOGIN [user1] WITH PASSWORD = 'abc';
END
GO  

USE DBExample
GO

IF NOT EXISTS
(
  SELECT * from sys.database_principals dp
  INNER JOIN sys.server_principals sp on dp.sid = sp.sid
  WHERE dp.name = 'user1' or sp.name = 'user1'
)
BEGIN
CREATE USER [user1] FOR LOGIN [user1]
END
GO

IF NOT EXISTS (SELECT * from sys.database_role_members WHERE USER_NAME(member_principal_id) = 'user1')
BEGIN
EXEC sp_addrolemember 'role1', 'user1'
END

GO

This creates a Login if it doesn't exist, goes to the database then creates a User if it doesn't exist, then associates a User with a Role.

edited Feb 4, 2013 at 17:54

answered Feb 4, 2013 at 17:32

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

confusedKid Over a year ago

I get an error saying "There is already login named 'user1' in the database.

Ian Preston Over a year ago

Well, I suspect there is already a database user mapped to the user1 login. I've updated the code to handle this case.

Community · Accepted Answer · 2017-05-23 12:12:30Z

I used the answer found here: https://stackoverflow.com/a/6159882 to make use of variables to substitute the user name to get around the 'There is already login/user named xxx in the database' error SSMS was complaining about. The code looks like this at the end:

USE DBExample
GO

DECLARE @userName varchar(100)

SET @userName = 'user1'

IF NOT EXISTS (SELECT * from sys.server_principals WHERE name = @userName)
BEGIN
DECLARE @LoginSQL varchar(200);
SET @LoginSQL = 'CREATE LOGIN ' + @userName + ' WITH PASSWORD = abc';
EXEC (@LoginSQL);
END

IF NOT EXISTS (SELECT * from sys.database_principals WHERE name = @userName)
BEGIN
DECLARE @UserSQL varchar(200);
SET @UserSQL = 'CREATE USER ' + @userName + ' FOR LOGIN ' + @userName;
EXEC (@UserSQL);
END

IF NOT EXISTS (SELECT * from sys.database_role_members WHERE USER_NAME(member_principal_id) = @userName)
BEGIN
EXEC sp_addrolemember 'role1', @userName
END

GO

Collectives™ on Stack Overflow

Create database user conditionally syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related