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--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son

thx. Any creative safeguard might help. For instance the documentation on EXECUTE AS looks to me like it will defer to permissions at the table level, not the proc level. Here is a link to some 2012 stuff that I think also applies to 2008. We run std 2008. I'll give this a whirl in the mean time.

EXECUTE AS didnt seem to offer any kind of help. It seems to me that a person still needs the grant which overrides the credentials of the acct specified in the EXECUTE AS clause. Makes me wonder what the EXECUTE AS is for.

I'm thinking as a safeguard maybe some sort of naming convention that requires the the words READ and ONLY be in the proc name so dba can catch any attempt to put some sort of update etc in such a proc. Also, maybe a report for our auditors that identifies procs with these words in it that explicity contain any sort of update and/or execute another proc or udf.

thx Anthony. That didnt seem to work. I see my peer is a member of the deny data writer role. That makes me believe I set that up correctly. And yet he can execute (successfully) the update proc to which all he has is execute granted.

good questions. I'm in a situation (potentially) where an auditing firm is grading us on how well we comply with Sarbanes. Somehow (maybe indirectly) they've talked one of my peers into prohibiting general use of procs (even if they are read only) on data we can query anyway. They are ok if job and service accts execute procs. I suspect that they are somewhat naive but I'm starting to understand their concerns. I'm still against the decision mostly because of what dynamic sql and parameter passing features bring to the table when using procs in sql server.

I suspect I can come up with a silver bullet that would satisfy everybody.

I'm preparing a case against their decision but have struck out on two of the more promising sagfeguards.