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
Entity Framework - Attribute in Clause Usage
How to Read the Contents of an .SQL File into an R Script to Run a Query
Inner Join in Update SQL for Db2
Insert All Values of a Table into Another Table in SQL
How to Get the Size of a Varchar[N] Field in One SQL Statement
Ad Hoc Queries VS Stored Procedures VS Dynamic SQL
Ora-00907: Missing Right Parenthesis
Divide by Zero/Null Workaround in Ssrs 2008 Report
Stored Procedure That Exports Data into CSV Files Only Exports to One File
What Is Full Text Search VS Like
Sqlite Auto-Increment Non-Primary Key Field
Rodbc Temporary Table Issue When Connecting to Ms SQL Server
Passing SQL "In" Parameter List in Jasperreport
Why Is Iterating Through a Large Django Queryset Consuming Massive Amounts of Memory
Get Execution Time of Postgresql Query
Postgres Error: More Than One Row Returned by a Subquery Used as an Expression
How to Calculate Session and Session Duration in Firebase Analytics Raw Data
Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)