I recently delivered a new version of a GP customization. The new version was deployed to a test environment, and right away, the user encountered an odd error.
Cannot find the object 'zDP_BIL10000SD_1', because it does not exist or you do not have permission.
Hmmm.
We confirmed that the stored procedure did exist. We confirmed that the DYNGRP had EXEC permissions to the procedure. We then tested with the sa login, and the error did not occur.
So that told us that we likely had a SQL permission issue. But we checked and double checked the permissions in SQL, and they looked fine.
I then did a test on my development machine and I was able to reproduce the issue as a non-sa user.
Hmmm.
Just to rule out the possibility that the GP customization had an issue, I created a new SQL server login, made it a user in a test company, and added it to DYNGRP. I then tried to manually run the stored procedure.
exec dbo.zDP_BIL10000SD_1 'GLJOURNAL','GLJOURNAL','3551','3551','6510'
And I got the same error message.
(0 row(s) affected)
Msg 15151, Level 16, State 1, Procedure zDP_BIL10000SD_1, Line 36
Cannot find the object 'zDP_BIL10000SD_1', because it does not exist or you do not have permission.
This seemed so strange. It didn't make sense.
I then stared at the error message for several minutes and it started to dawn on me. Do you notice anything unusual about it?
Let's start with the fact that it is actually three different messages.
Message 1: (0 row(s) affected)
Hmm, that's interesting. This would seem to indicate that something ran successfully, even though an error is occurring.
Message 2: Msg 15151, Level 16, State 1, Procedure zDP_BIL10000SD_1, Line 36
Aha! This message seems to be telling me that there is an actual error INSIDE the stored procedure script. Well, that's progress.
So if I open the script, what is at line 36?
Line 36 is the GRANT EXEC statement.
Do you see the problem yet?
Why would the stored procedure need to call a GRANT statement itself? Why would a GRANT statement exist inside the stored procedure CREATE script?
Well, if you look above the grant statement, what's missing?
A GO statement at the end of the stored procedure script.
Ugh.
Somehow, the GO statement was missing from two of the stored procedure scripts. I suspect that the GRANT was manually added to the scripts, and I didn't notice that the proc scripts didn't end with GO.
So when the proc was created, it included a GRANT statement where it would set permissions on itself. While obviously not intended, that does technically work. But it requires elevated rights on SQL, such as those an sa user has.
But when a standard DYNSA user attempts to run the proc, the GRANT statement will fail, because the user does not have permissions to run a GRANT statement.
Just when I think I've seen and made just about every mistake possible, I'm reminded that there are many more out there.
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
No comments:
Post a Comment