Could not find stored Procedure error

Question

Could not find stored Procedure error

M Joomun

SSCrazy

Points: 2018
More actions
August 30, 2018 at 6:04 am

#372151
Hello,
Using SQL Server 2014, Visual Studio 2015 and IIS 8.0.9, using Windows Authentication
I'm at my wit's end with this issue and have not been able to find an answer that resolves the problem I'm experiencing so I hope someone can help.
I recently migrated a SQL Server 2008R2 database (made and backup and then restored onto the new server) to SQL Server and made some IIS configuration changes to point to the new server. Everything worked well until the users asked me to create some new functionality.
I created the requested functionality (a stored procedure with three search parameters), tested in the IDE (Visual Studio). The new web page worked as expected and returns the expected dataset given the criteria entered.
I then deployed to our development IIS server, which is when the problem started. Although all the functionality that I originally developed under SQL Server 2008 works as expected, when I navigate to the new webpage get the error "Could not find stored procedure 'Procedure name'". I've checked all the usual things:
1. Have checked spelling of stored procedure and it's correct
2. Have checked login credentials of database user in my connection string by logging in to SSMS using same credentials and executing stored procedure. Can do both both.
3. Checked user permissions to stored procedure. User is a member of 'db_execute' role, which I think is OK
4. Have checked that login credentials match those in IIS. They do as all other functionality is working and they all use the same login credentials
5. Have run a SQL Profiler trace. It doesn't find any issues that I can see
6. Have deleted and re-created the stored procedure a number of times. No change
7. Have removed the application from IIS and recreated. No change
8. Have installed on a server running a different version of IIS (6). I get the same error
At the moment I'm not sure where the problem lies. Is it in IIS? Well why does some functionality work and not others? SQL Server? I've compared the properties of new stored procedure to those developed in SQL 2008 and I can't see any obvious differences.
Has anyone seen anything similar or maybe provide some advice as to what I should do next? Any help gratefully accepted.
(I can provide T-SQL if requested, though I'm not sure that's where the problem lies)
June 14, 2021 at 3:51 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 1

A couple of thoughts off the top of my head: Is the procedure created in the same schema that the db_execute role has access to? Is the procedure schema qualified and in the default schema for the user? You may need to explicitly allow execution on a schema ie.
GRANT EXECUTE ON SCHEMA :: theschema TO db_execute;

M Joomun SSCrazy Points: 2018 More actions · Answer 2

Hello,

All the stored procedures (old as well as new) have been created using the dbo schema. Should I still run your SQL?

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 3

It depends how your role has been set-up. It may be that the role has been set up so that each individual stored proc needs to be explicitly permissioned for the role. If thats the case, you'll need something of the form:
GRANT EXECUTE ON OBJECT::dbo.YourProc TO db_Execute;

If you run
GRANT EXECUTE ON SCHEMA :: dbo TO db_execute;

You'll allow the execution of all stored procs without explicitly needing to apply permissions to new ones.

M Joomun SSCrazy Points: 2018 More actions · Answer 4

Dwayne Dibley - Thursday, August 30, 2018 7:06 AM
It depends how your role has been set-up. It may be that the role has been set up so that each individual stored proc needs to be explicitly permissioned for the role. If thats the case, you'll need something of the form:
GRANT EXECUTE ON OBJECT::dbo.YourProc TO db_Execute;
If you run
GRANT EXECUTE ON SCHEMA :: dbo TO db_execute;
You'll allow the execution of all stored procs without explicitly needing to apply permissions to new ones.

OK. Tried the second bit of you code our and get 'Cannot find the user 'db_execute', because it does not exist or you do not have permission'.

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 5

Is it a context issue? Blind guess, but perhaps you're not fully qualifying the name, and so it's looking in master on the deployed version, while your local one knew to look at a specific database?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

M Joomun SSCrazy Points: 2018 More actions · Answer 6

jonathan.crawford - Thursday, August 30, 2018 7:57 AM
Is it a context issue? Blind guess, but perhaps you're not fully qualifying the name, and so it's looking in master on the deployed version, while your local one knew to look at a specific database?

Hello Jonathan,

I don't think so. The context is qualified in the connection string in my web.config file. The same connection string has also been configured on IIS.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

db_execute is not one of the fixed built-in database roles. I think the point that Dwayne and Jonathan are making is that this must be a manually created database role and may not have all of the permissions you are expecting. I've setup permissions like this at the schema level many times. Are you sure you were using the database in question when you ran the code:
GRANT EXECUTE ON SCHEMA::dbo TO db_execute;
You said you already verified that the user is a member of that role. You can see all the permissions that role has like this:
SELECT m.principal_id, m.name AS user_role, p.state_desc, p.permission_name, s.name AS schema_name, o.name AS object_name FROM sys.database_principals m INNER JOIN sys.database_permissions p ON m.principal_id = p.grantee_principal_id INNER JOIN sys.objects o ON p.major_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE m.name = 'db_execute' ORDER BY s.name, o.name, m.name;

M Joomun SSCrazy Points: 2018 More actions · Answer 8

Chris Harshman - Thursday, August 30, 2018 11:05 AM
db_execute is not one of the fixed built-in database roles. I think the point that Dwayne and Jonathan are making is that this must be a manually created database role and may not have all of the permissions you are expecting. I've setup permissions like this at the schema level many times. Are you sure you were using the database in question when you ran the code:
GRANT EXECUTE ON SCHEMA::dbo TO db_execute;

Apologies if I've misunderstood something and thanks for the reply. Originally I would have done this:

use MyDatabase

go

CREATE ROLE db_execute
GRANT EXECUTE TO db_execute

I've repeated my steps that I made (or thought I made) originally and re-run the line of code Dwayne suggested which now works and attached an image some of the properties of the db_execute role.

Is there anything else I should be checking?

M Joomun SSCrazy Points: 2018 More actions · Answer 9

Just to keep everyone updated and in case it helps someone else, I finally resolved this problem:

The connection string was pointing to the SQL Server using the Server name. I changed this to the IP address of the machine and hey presto it works!

DinoRS SSCrazy Points: 2683 More actions · Answer 10

if it works by IP you might have missing SPNs for your SQL Services.

M Joomun SSCrazy Points: 2018 More actions · Answer 11

DinoRS - Tuesday, September 18, 2018 6:13 AM
if it works by IP you might have missing SPNs for your SQL Services.

Thanks for the tip!

Sue_H SSC Guru Points: 90860 More actions · Answer 12

M Joomun - Tuesday, September 18, 2018 6:19 AM
DinoRS - Tuesday, September 18, 2018 6:13 AM
if it works by IP you might have missing SPNs for your SQL Services.
Thanks for the tip!

Except that usually indicates a DNS issue. Try using a FQDN to see if that works.

Sue

M Joomun SSCrazy Points: 2018 More actions · Answer 13

Sue_H - Tuesday, September 18, 2018 8:24 AM
M Joomun - Tuesday, September 18, 2018 6:19 AM
DinoRS - Tuesday, September 18, 2018 6:13 AM
if it works by IP you might have missing SPNs for your SQL Services.
Thanks for the tip!
Except that usually indicates a DNS issue. Try using a FQDN to see if that works.
Sue

Thanks Sue,
I used 'ipconfig /flushdns' from the commend prompt to refresh the DNS. I can now use the host name instead of the IP address in my connection string.