Create Users Dynamic Names And Assgin Roles

I need to create user with dynamic names with a variable name example : The following code gives a syntax error. Create Login @User_name WITH PASSWORD @password; U

Solution 1:

You can not use create login with variables. You have to create the statement dynamically or you can 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)

Solution 2:

You cannot use variables for object names. You can cheat

exec sp_addlogin @User_name, @password;

Either that or construct dynamic SQL, but make sure to use QUOTENAME to prevent SQL injection.