0

I just created my first database yesterday. It is now set up and I can write to it from my C# application using the admin user login. I would now like to use a limited write only user so my admin credentials are not stored in my application.

I have created a second login on master using query1

CREATE LOGIN writeOnlyLogin WITH password='myPassword';

I created a user on master using query2

CREATE USER writeOnlyUser FROM LOGIN writeOnlylogin;

I then added this user to the database that has the table I want this user to write to by using query2 with a connection string targeting the new database instead of master.

I granted insert access for this user only on a single table within the new database using query3

GRANT INSERT ON myTable TO writeOnlyUser

The user has no other roles or permissions.

In my c# application I have a datastructure that I want to write to this table. I create the connection string like this and write like this. With the admin credentials the open succeeds. With my new user the open fails.

public void SendToDatabase()
{    
    string serverName = "myServer.database.windows.net";
    string databaseName = "myDatabase";
    string tableName = "myTable";
    string user = "writeOnlyUser";
    string password = @"myPassword";
    string sqlConnectionString = $"Data Source={serverName};Initial Catalog={databaseName};User id={user};Password={password};";
    using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = HelperMethods.CreateSqlCommandFromObject(this, tableName, sqlConnection);
        int rowsAffected = sqlCommand.ExecuteNonQuery();
    }
}

The Exception message is, "Login failed for user 'writeOnlyUser'."

I am failing to find anything useful through google, however that could be because I don't know what I'm looking for. How can I fix this issue? In general, how can I debug login details?

0

My issue was that I misunderstood how logins work in SQL. I was using the USER name and the LOGIN password instead of the LOGIN name and the LOGIN password. It was a noob problem. Hopefully this saves someone else the time.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.