How to allow to execute stored procedures but not writes?

Question

I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data from all tables and views, but not to update or to insert (i.e. read anything and write nothing).

Can I set this up without specifically giving rights to each function or stored procedure by name, but rather grant him execution right on any function or stored procedure, just taking away the right to modify tables?

Will anything change if I run SQL Server 2008 instead?

Clarifications and additions:

1. If a stored procedure changes data, the user should receive an error message (either denying the modification or refusing access to the stored procedure completely).
2. If a potential solution involves denying permissions, can I just not grant certain permissions instead of denying?
3. Can a deny be applied on all tables, views etc. (existing now and in the future) in the database in one statement?

Answer 1

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:

-- The user-defined role containing all required permissions
CREATE ROLE Readers AUTHORIZATION dbo;

-- Give read-only access to all tables,
-- views on those tables, and in-line
-- functions
ALTER ROLE db_datareader ADD MEMBER Readers;

-- Example: add a user (Bob) to the role
ALTER ROLE Readers ADD MEMBER Bob;

After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.

You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.

One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.