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.

Perhaps there is a different *default schema* used? What happens when using the full 3-part name (`db.schema.obj`)? Permission errors are explicit about relating to permissions, which is why my hypothesis is a name resolution error. — user2864740, Jan 01 '22 at 10:00
Check the [grant execute documentation](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure?view=sql-server-ver15). There are question in stackoverflow regarding roles and permissions like this one [GRANT EXECUTE to all stored procedures](https://stackoverflow.com/questions/9321334/grant-execute-to-all-stored-procedures) — Cleptus, Jan 01 '22 at 10:06
As already noted - your code does NOT use `conString`. And stop using [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) - which is effectively what your code does. — SMor, Jan 01 '22 at 12:57
hi SMor, those connection string variables `cs` and `conString` were just a typo in my code formatting when pasting the code here in StackOverflow, which I have updated here accordingly. The core issue still exists regarding stored procedure not being found, I will try some other options as mentioned in the comments above. In the meantime, would you have some additional inputs? thank you! — theITvideos, Jan 02 '22 at 00:29

score 0 · Answer 1 · edited Jan 01 '22 at 10:12

0

I think something is wrong in your code with the connection string set to the SqlConnection instance, you must use conString variable instead of the cs variable:

using (var con = new SqlConnection(conString))

edited Jan 01 '22 at 10:12

Cleptus

3,446
4
28
34

answered Jan 01 '22 at 10:10

Keyvan Soleimani

606
2
4
16

Thanks for the reply Keyvan. That connection string variable was just a typo in my formatting when pasting the code here in StackOverflow, and now I have updated my code accordingly in stack overflow. The actual issue regarding 'Could not find stored procedure 'usp_MyTestSP' still exists, would you have some input on that? thank you! – theITvideos Jan 02 '22 at 00:20

score 0 · Accepted Answer · answered Jan 05 '22 at 00:06

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();
}

Could not find stored procedure error when using username password combination

2 Answers2