Creating dynamic user names and assgin roles

Question

I need to create a user with dynamic names with a variable name

example:

The following code gives a syntax error.

Create Login @User_name WITH PASSWORD @password; USE database; 

and I need to assign a role to the created user.

Answer 1

You cannot use variables for object names. You can fool

 exec sp_addlogin @User_name, @password; 

Either this, or build dynamic SQL, but be sure to use QUOTENAME to prevent SQL injection.

Answer 2

You cannot use create login with variables. You must create the statement dynamically or use sp_addlogin. According to http://msdn.microsoft.com/en-us/library/ms173768.aspx sp_addlogin is deprecated.

 declare @UN sysname declare @PW sysname declare @S nvarchar(100) set @UN = 'UserName' set @PW = 'Password' set @S = 'CREATE LOGIN ' + quotename(@UN) + ' WITH PASSWORD = ' + quotename(@PW, '''') exec (@S)