Hi. It looks like granting a user permission to execute a proc overrides that user's insert/update/delete permissions they'd otherwise have on a table that the proc updates.
I was hoping for the opposite and wonder if there is an elegant way for the following to never occur...
proc x selects data from a table and we grant execute on that proc to user a.
IT changes the proc to do an update but forgets to remove the grant execute.
Now the user can update the table
that is the expected behavior: if you grant EXECUTE to a proc, then as long as the objects affected all exist under the same owner(ownership chaining, the procedure can insert/update/delete from the table, even when the calling user has no access to those tables at all..
the idea is to give the ability to remove direct access to the underlying objects, and only grant permissions to specific, desired functionality, like a procedure.
in your case, since you stated the same procedure 's code changed from select to update, you'd want to drop adn recreate the procedure to remove the previous permissions on it, i think.
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!