Stored Procedures' not found

(0) Share

Report

Posted on by Community Member

We are running into a situation where stored procedures that we create are not found when other users are logged in and testing our changes. The stored procedures have all been granted execute privileges to DYN_GRP . Re-applying the privilege doesn't seem to work. Dropping and recreating the procedures does work. Any ideas as to what we could be missing? I'd hate to have to recreate stored procedures frequently so that the permissions are corrected.

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (1)

Suggested answer

Lisa at AonC.com 963 Super User 2025 Season 2 on at

Like (1)

Report

When SQL permissions are giving me trouble, I start with the grant.sql (found in the SQL\Util folder of your GP installation). More often than not, that resolves issues for the GP SQL logins.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Lisa. We have tried running that sql before, but it does not seem to resolve this issue.

Was this reply helpful? Yes No
Tim Wappat 5,711 on at

Like (0)

Report

Can you script them out and compare with what you are recreating with WinMerge or similar?

Are they being created in the wrong schema perhaps?

Seems very odd!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thought about the schema and checked everything is in the right schema. running the stored procedure through SSMS works and running GP as myself works, but for other users it comes up as not found. Then I drop and recreate the procedure. Everything works for everyone.

Was this reply helpful? Yes No
Tim Wappat 5,711 on at

Like (0)

Report

Try running the following SQL to list permissions before and after recreating the stored procedure. Hopefully there will be a difference!

SELECT dp.NAME AS principal_name
,dp.type_desc AS principal_type_desc
,o.NAME AS object_name
,o.type_desc
,p.permission_name
,p.state_desc AS permission_state_desc
FROM sys.all_objects o
INNER JOIN sys.database_permissions p ON o.OBJECT_ID = p.major_id
LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE o.NAME = 'glAdjustingPeriod'

Change "glAdjustingPeriod" for one of your procedures.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Tim. There was a difference, but now I am a little confused as to why this difference. Is this what I should expect?

First before running it I get this type of error:

Its a different object than the one I ran the sql for, but this is the same error we are getting on lots of SPs.

Before running the Drop and Create on the procedure gave me this.

After I recreated the procedure I tested the form and I did not get an error for that procedure, so it seems to have worked.

I ran the SQL again and this time I got no records. The DYNGRP permission was gone.

I re ran the Grant execute for the stored procedure again. Re ran the SQL you sent me. Now it shows the same exactly as the one I ran before I dropped and recreated the procedure. Tested the procedure in action again and got the Can not find procedure error again.

It is almost as if it is doing the opposite of what it should be doing. With DYNGRP it can not find it, without DYNGRP it seems to find it. ???

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Hi Erick,

Can you please post the content of your SP here (if it's not too long) ? under which security context is it executed ? from which application are you calling this SP ? When you say that you have no problem to run the SP but your users facing an error, under which account are you logged in ? in which database is the SP saved ? system or company DB ?

Sorry for all the questions, but they are important to get a global picture of your context..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Beat,

Here's one that is not too long and is an example of one that seems not found with DYNGRP granted execute privilege, but works fine when not granted it.

CREATE PROCEDURE [dbo].[RMC_CheckCertForDuplicate](@RMC_Company integer, @RMC_NextCertificateNum integer, @RMC_Class varchar(2), @rmc_Prefix varchar(2), @cnt integer output)

AS

Select @cnt = count(*)

from RMCSTK_CERTIFICATE

where RMC_Company = @RMC_Company and RMC_NextCertificateNum = @RMC_NextCertificateNum and RMC_Class = @RMC_Class AND rmc_Prefix = @rmc_Prefix;

GRANT EXECUTE ON dbo.RMC_CheckCertForDuplicate TO DYNGRP

Not a very complex one as you can see. It is basically getting information from a custom table and returning it to the calling form.

We are calling it from within Dynamics and any user should be able to run it. I login as myself, as a super user when everything works, I also have a test user account that has the same limited access as our users. When I use the test account it gives me the error the same way asit does for everyone else.

Was this reply helpful? Yes No
Verified answer

Tim Wappat 5,711 on at

Like (1)

Report

You should have a GO between

rmc_Prefix = @rmc_Prefix;

GO;

GRANT EXECUTE ON dbo.RMC_CheckCertForDuplicate TO DYNGRP;

GO;

Otherwise your stored proc will try to grant permissions, each time it runs that is only possible with correct user permissions. Could this be it?

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

Yup,

Agree with the Tim,

The way the GRANT is placed will not work.. Also just to be clear, this is not standard to put the GRANT as integral part of the SP, because that's what it currently looks like.. (withotu the GO in between..

Was this reply helpful? Yes No