Create users dynamic names and assgin roles

Question

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;
                USE database; 

and i need to assign a role for created user ..

Answer 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)

Answer 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.