Role for read-only access to execute SP/get result set, without indirectly modifying db?

For example

if a SQL Server user account is given only the DataReader role and ability to execute one stored procedure that modifies data in some way, should the execution of that stored procedure by that user cause the modification to occur?

Overall, I want to give one user only read ability to the entire database including use of SQL syntax, Views and to execute any store procedures that return result sets. But I don't want any side effects开发者_开发技巧 to cause changes in the database. Therefore in the aforementioned stored procedure example, the attempt would ideally error out to satisfy my requirement, and all similar scenarios where a side-effect might cause a change. I want to ensure my database is protected against those.

Is this doable simply at the role level?

Product: SQL Server 2005 and up

Sure you can do this. Simply create a database role at the database level, and grant that role read on the tables and execute on only the stored procedures you want (i.e. the ones that read). Then, add the desired user(s) to your database role.

However, all things considering, if you are using stored procedures to read data, do so completely and do not grant read on tables for users of any level. Drive all data access through stored procedures (and views).

EDIT: Just noticed you said SQL 2005 "and up." If you are using SQL Server 2008, look at application roles instead of the traditional database roles.