Permissions to use SPs and UDFs that don't update data

For security reasons, we developers don't have access to our live databases. However, read-only access is temporarily granted when we need to investigate specific problems that are happening in live. The problem is that we simply get added to the db_datareader role, which allows us to select from tables, but not to run any SPs or UDFs even if they only read data and don't perform any updates.

Short of getting permissions granted specifically for every SP or UDF we might need, is there a global way of granting such permissions?

I think functions do not have the ability for disk I/O, so it should be safe to grant execute on those. Which version of SQL server are you using?

running this in your database:
GRANT EXECUTE ON SCHEMA :: dbo TO <YourDatabaseUser/Group>

should allow you to run all the SP. However some of these might alter data so this will open a backdoor.

Bill: I'm using 2008 R2. You may be right about the functions - I thought that was the case but I wasn't sure.

DS: Loads of our SPs update data, so we can't grant execute permissions on everything. I'm looking specifically for a role that allows you to run any SP and functions that don't update data.

One idea, create a database on the same instance, add all your stored procedures to that database and grant devs execute. Then grant devs read to the original database. There will be an issue with the stored procedures calling tables on the original database, but I think you should be able to resolve that using synonyms.

Thanks, Bill - I'll give that one some thought. Our databases are huge with hundreds of tables, procedures and functions, so it may not be practical - but it's better than anything I've come up with myself.