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?
Best Answer
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.