On a related note, we have a project in development for our internal account managers and tech support team to be able to run any select query they want against our databases to help them resolve issues. The devloper asked me how she could go about making sure they didn't inadvertantly perform some query other than a select. My reply was to create a user account that had all privelages denied except for select and execute the procedure as that user account. Bear in mind that the AM's and tech support will be using a web app that is using an account whose only rights is to execute stored procedures.
This is the real code that was finally used, but is the initial test code I sent to the developer for testing how it would work. Bear in mind that this is SQL 2005 code.
-- Create server login for test
Login DBDataReaderOnly With Password = 'PeasPorridgeCold'
-- Create database user for login
User DBDataReader For Login DBDataReaderOnly With Default_Schema = dbo
-- Create databse role for easy assignment of permissions
-- Grant select rights
Select To db_DataReaderOnly
-- Deny all other permissions
Execute, Insert, Update, Delete, References, Alter, Take Ownership, View Definition To db_DataReaderOnly
-- Assign user to the database role
sp_addrolemember 'db_DataReaderOnly', 'DBDataReader'
-- Create a test procedure
Execute As 'DBDataReader'
-- Test new account, run each command individually
dbo.USP_DynamicDataTest @SQL = 'Select * From TestTable with(nolock)'
dbo.USP_DynamicDataTest @SQL = 'Delete Top (1) From TestTable'
dbo.USP_DynamicDataTest @SQL = 'Drop Table TestTable'