Could not find stored procedure error when using username password combination

Question

I am trying to call a stored procedure from my C# code and passing table parameters using the user-defined table types.

This works perfectly fine when I use the Trusted_Connection= True; method in the connection string. However, when I update the connection string to use the service account username password combination, I get the following error:

Could not find stored procedure 'usp_MyTestSP'

By the way, here is the code I'm using to call that stored procedure and passing the table parameters:

// Works fine with this connection string
var conString = "Data Source=xxx;Initial Catalog=xxx;Trusted_Connection=True;"; 

// Fails when using a service account in connection string. Error: Could not find stored procedure 'usp_MyTestSP'
//var conString = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"; 

using (var con = new SqlConnection(conString))
{
    var cmd = new SqlCommand("usp_MyTestSP", con) {CommandType = CommandType.StoredProcedure};

    var myParams = new SqlParameter()
                {
                    ParameterName = "@udt_RecordProcessed",
                    Value = myVal
                };
    cmd.Parameters.Add(myParams);

    con.Open();
    cmd.ExecuteNonQuery();
}

Is it really the permission thing?

When I connect to the SQL Server instance using the custom service account, I am actually able to view that stored procedure that the code is complaining about not being able to find.

Do I need some additional permissions here for my service account to be able to execute that stored procedure?

Or... is there something I need to update in my code for it to work with the service account? Although the same code works just fine with using the trusted connection.

Any input here would be really very helpful.

Thank you.

Answer 1

I was able to get this working by adding the [db_owner] prefix to the stored procedure name usp_MyTestSP as shown in the following code:

// Works fine with this connection string
var conString = "Data Source=xxx;Initial Catalog=xxx;Trusted_Connection=True;"; 

// Fails when using a service account in connection string. Error: Could not find stored procedure 'usp_MyTestSP'
//var conString = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"; 

using (var con = new SqlConnection(conString))
{
  // ** The fix was to prefix the stored procedure name with [db_owner]
    var cmd = new SqlCommand("[db_owner].[usp_MyTestSP]", con) {CommandType = CommandType.StoredProcedure};

    var myParams = new SqlParameter()
                {
                    ParameterName = "@udt_RecordProcessed",
                    Value = myVal
                };
    cmd.Parameters.Add(myParams);

    con.Open();
    cmd.ExecuteNonQuery();
}