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?

Brad · Accepted Answer · 2010-10-18 12:31:58Z

3

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

edited Oct 18, 2010 at 12:31

answered Oct 16, 2010 at 18:26

Brad

15.7k6 gold badges40 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

madlan Over a year ago

I was trying to avoid this due to SQL injection.

Brad Over a year ago

@madlan, duely noted, but you can always check for mal-characters in your login name before submitting. Semi-colon, quotes, and double hyphens are a good starting point. Personally, I never allow these characters in usernames anyways.

gbn · Accepted Answer · 2010-10-17 16:16:02Z

3

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

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

answered Oct 17, 2010 at 16:16

gbn

434k84 gold badges602 silver badges690 bronze badges

3 Comments

madlan Over a year ago

I'm using .net 4 so cannot use SMO at the moment.

gbn Over a year ago

You'd use dmo for 2000. But CREATE LOGIN or USER is SQL Server 2005+

madlan Over a year ago

SMO appears to be ok with 2000 (SSMS uses it when connected to a 2000 instance).

Collectives™ on Stack Overflow

SQL Server CREATE USER with Parameters (VB.net)

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related