I am testing a method for users to only have the ability to execute only stored procedures that return data and not be able to execute procs that modify data. For testing purposes I have created a 'select' procedure and an 'insert' procedure. I created a user with db_datareader and execute permissions on the two procs and I was still able to execute the 'insert' proc as this user. I also attempted to deny insert permissions on the referenced table to no avail.
Short of establishing a role and granting execute permission to the appropriate procedures, is there a simpler way to do this?
That is how GRANT EXECUTE works. This is what we do at our site. All users have SELECT permissions (we use a role) and EXECUTE permission on all procedures, only the procedures can INSERT/UPDATE/DELETE. So the Role the users are assigned too can not modify data only read (SELECT), however they can modify data throught a controlled stored procedure. Once you give a user EXECUTE permission you then give them the permission to modify the underlying table, only through that procedure. They still can not write there own UPDATE/INSERT/DELETE statements.