Creating database logins and users from user input

Question

I am using ASP.NET (VB.NET) with SQL Server and wish to allow a user to create server logins and database users from a web form.

I have gotten this working by executing commands that create the login and user:

Dim cmd As New OleDb.OleDbCommand(
    "CREATE LOGIN [" & login & "] FROM WINDOWS WITH DEFAULT_DATABASE = Db; " & _
    "CREATE USER " & username & " FOR LOGIN [" & login & "]; ",
    connection)
cmd.ExecuteNonQuery()

The login and username are user inputs that I am sanitizing (as best I can) and inserting directly in the CommandText. While this is working, I don't feel as if I should be doing it this way.

I'm hoping for something similar to parameterized queries, like so:

Dim cmd As New OleDb.OleDbCommand(
    "CREATE LOGIN [@login] FROM WINDOWS WITH DEFAULT_DATABASE = Db; " & _
    "CREATE USER @username FOR LOGIN [@login]; ",
    connection)
cmd.Parameters.AddWithValue("@login", login)
cmd.Parameters.AddWithValue("@username", username)
cmd.ExecuteNonQuery()

However, this does not work because CREATE LOGIN and CREATE USER do not seem to allow parameterized values, as mentioned by podiluska:

Incorrect syntax near '@username'.

Is there a better way of creating logins and users from user input?

@Jupaol: OP wants to create database logins, not memberhip users. — Tim Schmelter
– Tim Schmelter, Commented Jul 24, 2012 at 19:32
@WaqarJanjua I hadn't considered it at first since sp_addlogin is deprecated and parameters didn't work for me. Thanks for the mentioning it though - your comment lead me to find this. — Zhihao
– Zhihao, Commented Jul 24, 2012 at 21:54

podiluska · Accepted Answer · 2012-07-24 19:38:48Z

1

You can't parameterise the CREATE USER and CREATE LOGIN commands.

You could create a stored procedure that uses parameters on the (deprecated) sp_addlogin and sp_adduser, but to create the user with a windows login, you need to use CREATE LOGIN and hence dynamic SQL.

edited Jul 24, 2012 at 19:38

answered Jul 24, 2012 at 19:32

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:34:51Z

0

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"

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 24, 2012 at 21:44

Zhihao

14.7k2 gold badges29 silver badges36 bronze badges

Collectives™ on Stack Overflow

Creating database logins and users from user input

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related