Re: Security privileges on stored procedure foibles?

[Terry Farless]

Hi Archil,

Not sure if you ever received a reply to this message but I have just encountered something VERY similar that I'll share. I have a user that accesses stored functions on my database (set up via GRANT EXECUTE...). Until yesterday, this user also had a GRANT SELECT... on one of my tables. I realized that the user no longer needed the table SELECT so I revoked that privilege, leaving the user with only EXECUTE privileges for the stored functions. This caused ALL function calls to fail with the error msg "Access denied for user..."

Oddly enough, once I added a SELECT privilege back, everything worked fine. The weird thing is that the table I gave SELECT privilege to doesn't have anything to do with the stored functions! Apparently all the user needed was SELECT to *ANY* database table and the EXECUTEs were once again working.

I hope this helps.

Terry