SQL Server CREATE USER with Parameters (VB.net)

Question

I'm trying to execute CREATE USER with a given username (via sql parameter)

exec sp_executesql N'CREATE USER @LoginName 
                        FOR LOGIN @LoginName;',
                   N'@LoginName varchar(5)', @LoginName='myuser'

Heres the code thats generating the above:

Dim myCommand As SqlCommand = New SqlCommand("CREATE USER @LoginName 
                                                 FOR LOGIN @LoginName;", 
                                             ClassDatabaseConnection.CustomInstance)
myCommand.CommandType = CommandType.Text
myCommand.Parameters.Add("@LoginName", SqlDbType.VarChar).Value = LoginName
myCommand.ExecuteScalar()

I get and error:

Incorrect syntax near '@LoginName'.

I think this is due to the parameters being passed as VarChar causing 'MyUser' rather than MyUser

Can I not do this with sql parameters?

Answer 1

You cannot use parameters with a Create User statement. Since you're already in VB, try piecing together a statement.

Dim myCommand As SqlCommand = New SqlCommand("CREATE USER " + LoginName +
                                                " FOR LOGIN " + LoginName + ";", 
                                             ClassDatabaseConnection.CustomInstance)
myCommand.CommandType = CommandType.Text
myCommand.ExecuteScalar()

Answer 2

As noted, you can't parameterize a CREATE LOGIN.

To avoid SQL injection (also as noted), consider using SMO Login.Create