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
Yes.. absolutely.
Thanks Tim for raising it to me :-)
This appears to be it. Simple fix!! I looked at each script our developer did that is causing the issue and the same mistake was made. So when the developer initially coded the procedure, not having the GO made it save to the procedure like another statement. Is that right?
Thanks very much for your help Tim and Beat!
Team Bucher/Wappat do it again!
Thanks Beat
Tim.
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..
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?
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.
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..
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. ???
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (