Small mistakes can cause big headaches

By Steve Endow

Several months ago I developed a GP customization.  It worked fine, the partner deployed it, and then it was tested.  So I thought.

Today, I got a message saying that the customization works for the sa login, but not for a standard GP login.  A standard GP user will get an error that the save failed.

That's a great start, as it tells us quite a bit.  If it works for sa, but doesn't work for a GP user, then there's a 99% chance it's a SQL permission issue.

So I send the partner several GRANT statements to ensure that DYNGRP has access to the custom tables and custom stored procedures.  The partner runs those scripts, but the error persists.

I do a test on my development server, and sure enough, I can reproduce the problem.  It works fine for sa, but doesn't work for a test GP user.

Clearly I made a classic developer mistake.  It worked just fine with Administrator and sa, but obviously I didn't test with a standard user.

So I trace the SQL that is being called and see that it's a call to a stored procedure.  When I run the proc as sa, it runs fine.  But when I run it as a test GP user, I get this error.

(0 row(s) affected)
Msg 15151, Level 16, State 1, Procedure zDP_MEMBIL10000SD_1, Line 34
Cannot find the object 'zDP_MEMBIL10000SD_1', because it does not exist or you do not have permission.

I confirm the procedure does exist.  And I confirm that DYNGRP does have permission.  I also confirm that the custom tables and procs are under the dbo schema, as I've had that issue before.

So everything looks fine.  Objects look fine.  Permissions look fine.

So I then script out the stored procedure from the TWO database to see if it is calling some other object that may be causing the issue.  And after reviewing it, I finally see the problem.

So do you see the problem?

Notice the GRANT statement at the bottom.  It was scripted as part of the stored procedure.  The GO keyword was missing after the last END statement, before the GRANT statement.  So the GRANT statement became part of the proc.

The sa user can run the GRANT statement, but the standard GP user cannot.

Once I updated the script to add a GO before and after the GRANT, the permission issue went away and the customization worked fine.

So a single missing word in a stored procedure just took an hour to research and resolve.

Fun times.

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.

You can also find him on Google+ and Twitter

http://www.precipioservices.com