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:
- 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).
- If a potential solution involves denying permissions, can I just not grant certain permissions instead of denying?
- Can a deny be applied on all tables, views etc. (existing now and in the future) in the database in one statement?