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.
Best Answer
That comment on MSDN simply means that there are some situations (such as sometimes when using dynamic SQL) where granting a user EXECUTE on the stored procedure will not necessarily be enough to allow them to execute it.
What you want cannot be done - if they are able to run a stored procedure, they will be able to do whatever the stored procedure does - INSERT, DELETE, UPDATE, whatever. Even SELECTs can make changes via SELECT INTO. And there's always temporary tables or table variables to complicate any attempts to identify a list of safe/unsafe sps.
My advice is to narrow down as much as possible what the read-only user should actually need to read, create a db role with EXECUTE permissions to just those objects, and start from there. And I hope you've not been giving EXECUTE permissions to the Public role, because if so you'll have to DENY EXECUTE on all the other stored procedures as well.