I want an easy way to make a read-only user on my database. The user should have permission to execute stored procedures only if they don't modify data. I don't want to figure out which stored procedures are read-only and give permissions that way because there are far too many to go through.
I thought this was possible after I read the following:
"A stored procedure can require both EXECUTE permission on the stored procedure and INSERT permission on several tables that are referenced by the stored procedure." -- http://technet.microsoft.com/en-us/library/ms191291.aspx
Unfortunately, I am able to EXECUTE an 'executable' stored procedure that INSERTs into a 'read-only' user table.