SQL Create Logon - Can't Use @Parameter as Username

SQL CREATE LOGON - can't use @parameter as username

Apparently CREATE LOGIN only accepts literals.
You could try wrapping it in an exec and building it as a string:


EXEC('CREATE LOGIN ' + quotename(@username) + ' WITH PASSWORD = ' + quotename(@password, ''''))

edit: added quotename for safety from sql injection attacks

Creating database logins and users from user input

One possible option is to use a stored procedure, which I had previously avoided due to sp_addlogin and sp_adduser being deprecated (as mentioned by podiluska).

However, it seems possible to use CREATE LOGIN and CREATE USER in a stored procedure by building them in a string with EXEC, which is described in this question. The parameterized query code snippet included in the question can then be adapted to use stored procedures with minor changes:

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_storedprocname"

Not able to create user and update user in Database from web service

How about we strip out anything non a-z in a username; it'll be pretty hard to inject anything as a result. This does introduce a rule that usernames must be ascii a-z, but does anyone really need a username of 'DROP TABLE Students;-- ? (If they really do, see bottom)

        public async Task CreateUserNameQuery(string username, string password)
{
username = Regex.Replace(username, "[^a-z]", "");

string query3 = $@"
IF DATABASE_PRINCIPAL_ID('{username}') IS NULL
CREATE USER {username} WITH PASSWORD={{0}}, DEFAULT_SCHEMA=[dbo]
ELSE
ALTER USER {username} WITH PASSWORD={{0}}, DEFAULT_SCHEMA=[dbo]";

await Database.ExecuteSqlRawAsync(query3, username, password);

await Database.ExecuteSqlInterpolatedAsync("EXECUTE sys.sp_addrolemember @rolename = N'db_datareader', @membername = {username}");
}

I'm fairly sure you can parameterize the password, as it's presented as a string constant normally - it's just the username that's presented as an identifier - but I'm not in a position to test it. If it doesn't work out let me know

Final note; you can't send GO to SQLS server; SSMS breaks scripts up using GO as a delimiter and you should do this too (here I make two queries to mimic either side of the GO)


If you really need your username to contain all sorts of wacky stuff, perhaps it would be simplest to ask the db to quote it for you, and then use the quoted value in a string. Pick on any suitable object in your domain model that has a string property, such as Order and execute a raw query that quotes the username into the string property (let's imagine orderreference)

var x = (await context.Order.AsNoTracking().FromSqlInterpolated($"SELECT -1 as OrderId, QUOTENAME({username}) as OrderReference").FirstAsync()).OrderReference;

x would now contain, for an input of abc[]def, [abc[]]def] which can be safely concatenated into an sql in a position that cannot be parameterized

Create users dynamic names and assgin roles

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.

doesn't quotename work inside of exec?

Best solution I could come up with for you is to pre-quote the value:

declare @var nvarchar(128);
set @var = 'hello world';
declare @quoted varchar(100);
select @quoted = quotename(@var);
exec('print ''' + @quoted + '''');


Related Topics



Leave a reply



Submit