Creating database user with domain in C# doesn't work

Question

I want to create a database User in C#. In Microsoft SQL Server Management Studio I just can create one with the following command:

CREATE USER [DOMAIN\user]

But when I try to do the same in C# I can't send a single backslash in the command.

string query = @"CREATE USER [DOMAIN\user]";

I already tried things like query.Replace("\\\\", "\\") or query.Replace(@"\\\\", @"\\"), but nothing worked for me. I always get an error with:

Incorrect syntax near '\\'.

It's the same like I would write CREATE USER [DOMAIN\\\\user] in SQL Server Management Studio.

Somehow I can't transfer just one backslash in the query.

Does anyone have a hint for me?

Thats my actual code:

public SqlConnection Conn;    
public bool CreateUser(string LoginName)
{
    string dom_login = Domain + "\\" + LoginName;
    string query = @"CREATE USER [" + dom_login.Replace("\\",@"\") + "]";

    // now query should be okay, so send to sql connection
    DataTable dt = new DataTable();
    if (Conn.State == ConnectionState.Closed)
         Conn.Open();
    try
    {
        SqlDataAdapter sDataAsp = new SqlDataAdapter(query, Conn);          
        sDataAsp.Fill(dt);
    }
    catch (Exception ex)
    {
        // Here i get the syntax error
        string error = ex.Message;
        return false;
    }
    return true;
}

Why can't you send a single backslash, should work with `@`, what error are you seeing? Double is defo wrong unless you don't use `@`, in which case you don't need `Replace` — Charlieface, Feb 14 '21 at 21:20
Creating domain users obviously works. SSMS doesn't use any system or API calls, it uses SQL commands too. Post your actual code and actual errors if any. — Panagiotis Kanavos, Feb 14 '21 at 21:24
`Incorrect syntax near '\\'.` because you added an extra slash when only one was needed. The string replacements, if they worked, would only make things worse.Luckily, they don't as you're trying to replace 2 or 4 backslashes with 1 or two respectively. — Panagiotis Kanavos, Feb 14 '21 at 21:24
You've asked this question before and you were asked for additional information. There is clearly something odd about your code, because the methods you use for string escaping work in general. So show the exact code which you are using. — Dale K, Feb 14 '21 at 21:26
`CREATE USER [Contoso\Fritz] ` works. So does `string query = @"CREATE USER [DOMAIN\user]";`. We can't guess how you tried to execute the commands though, or what errors you got. You certainly **didn't** get `Incorrect syntax near '\\'.` because there's only single backslash there. Post the *original* code and error — Panagiotis Kanavos, Feb 14 '21 at 21:30
i added my actual code, i hope you now see clearly what i'm trying. — Runner, Feb 14 '21 at 21:47
Well, I don't think you should be using a SqlDataAdaptor *The SqlDataAdapter serves as a bridge between a DataSet and SQL Server for retrieving and saving data.* - you are trying to execute SQL which is something else. I would be using `SqlCommand.ExecuteNonQuery`. — Dale K, Feb 14 '21 at 21:53
but it should work, even when i only got one or zero rows, it works for example with the query = "select name, database_id from sys.databases", then i of course get rows, but with create user i expect to just modify the table — Runner, Feb 14 '21 at 21:56
*Its should work* - why? Its clearly designed to connect to a datatable - who knows what its doing under the hood. Use the right tool for the job. — Dale K, Feb 14 '21 at 22:01
i edited again, but SqlCommand.ExecuteNonQuery didn't solve the problem. — Runner, Feb 14 '21 at 22:04
The query doesn't contain 2 backslashes, the debugger shows 2 backslashes because thats how a backslash is escaped in a string, i.e. 2 backslashes is actually only a single backslash escaped. — Dale K, Feb 14 '21 at 22:20
I just ran both of your code blocks and they both work, creating me a new user. And your code can be simplified to `string dom_login = Domain + @"\" + LoginName; string query = @"CREATE USER [" + dom_login + "]";`. — Dale K, Feb 14 '21 at 22:22
i see, but why do i get the same error everytime, it just transferes with two backslashes — Runner, Feb 14 '21 at 22:23
I can only imagine that your actual code is more complex than shown and something else is interfering? Are you running under ASP.NET? WinForms? Something else? Can you access the database with a create user command that doesn't require a backslash? I assume when you put a breakpoint on it shows `query = "Domain\\User"`? — Dale K, Feb 14 '21 at 22:28
thank you! i guess i had some trouble with @ or sth, because now it works — Runner, Feb 14 '21 at 22:29

score -1 · Answer 1 · answered Feb 14 '21 at 22:33

-1

Solved:

private bool ExecuteQuery(string domain, string user)
{
    try
    {
        string dom_login = domain + @"\" + user; 
        string query = @"CREATE USER [" + dom_login + "]";
        if (Conn.State == ConnectionState.Closed)
            Conn.Open();
        SqlCommand command = new SqlCommand(query, Conn);
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        error = ex.Message;
        return false;
    }

    return true;
}

answered Feb 14 '21 at 22:33

Runner

7
5

1

Thats nuts, because that is identical in function to your posted code - I used your posted code first, before changing it to this, and both worked. Oh well at least its working. – Dale K Feb 14 '21 at 22:35
ye i know it's strange, but thanks for help – Runner Feb 14 '21 at 22:37

Creating database user with domain in C# doesn't work

1 Answers1