Creating Login Programatically

Question

0.00/5 (No votes)

See more:

Good day buddies.

Please I have a problem and I hope you guys can help me out.

I am working on VB.NET 2012 application that connects to ms sql server. I want to allow users to select the installed sever instance of their chioce so I have a module that present installed server instances for the user to select from.

After a selection is made, the module will then create the necessary database objects. My code raise error when it tries to create ms sql login in for the current user. I found that the error is raised by connectionstring(firstSQL) but I do not know to resolve it.

Please find below details of my code.

VB

C#

connectionStr = "Data Source=" & lstLocalSevers.SelectedItem.ToString & ";DataBase=;Integrated Security=SSPI"

Dim firstSQL As New SqlConnection(connectionStr)


<pre>'create login for the current user
Dim sql As New SqlCommand("if not exists (select NAME FROM sys.login_token" & ")  CREATE LOGIN " & us, firstSQL)
            sql.ExecuteNonQuery()

The error: 'Incorrect syntax near '-'. The '-' is part of the computer name(name-PC) and the instance is name-PC\SQLEXPRESS and the onnection string generate from the pc is '

"Data Source=name-PC\SQLEXPRESS;DataBase=;Integrated Security=SSPI"

'

Thanks in advance for your help

Posted 17-Aug-14 13:38pm

noblepaulaziz

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Answer 1 · 2014-08-18T04:56:00

Solution 1

Two solutions:

1) (best): Parameterize your query to the command object and use AddParameterWithValue for the argument: ... LOGIN @us" ...

2) enclose your argument in single quotes: ... LOGIN '" & us "'" ...

The first will be immune from SQL injection, the second will not be!

Posted 18-Aug-14 4:56am

PhilLenoir

Comments

noblepaulaziz 2-Sep-14 11:18am

Thank you PhiLenoir for your reply. I am very sorry for the late reply. Your suggestion did not work. The 'us' is a string variable.

I found the solution. The problem was missing 'FROM WINDOWS'. The string should read:

Dim sql As New SqlCommand("if not exists (select NAME FROM sys.login_token" & ") CREATE LOGIN " & us & "FROM WINDOWS", firstSQL)