db042188 (1/21/2013)
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.
Lowell