Dynamic Login & User Creation

Hello. I'm trying to create a new login and username inside a trigger using variables.

This code works:

create login testUserName with password = 'testPassword'

The problem comes when I try to use variables for the username and password like this.

create login @.username with password = @.password

Does anybody know how to get around this problem?

BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '@.username'.

Msg 319, Level 15, State 1, Line 14

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||

This doesn′t work unless you wrap it in dynamic SQL:

DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = 'testUserName2';

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||

You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.