-
Jun 9th, 2014, 10:01 AM
#1
Thread Starter
Fanatic Member
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?
-
Jun 9th, 2014, 04:47 PM
#2
Re: Permissions to use SPs and UDFs that don't update data
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?
That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma
Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
-
Jun 10th, 2014, 04:43 AM
#3
Re: Permissions to use SPs and UDFs that don't update data
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.
-
Jun 10th, 2014, 06:07 AM
#4
Thread Starter
Fanatic Member
Re: Permissions to use SPs and UDFs that don't update data
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.
-
Jun 10th, 2014, 12:47 PM
#5
Re: Permissions to use SPs and UDFs 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.
That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma
Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
-
Jun 11th, 2014, 11:01 AM
#6
Thread Starter
Fanatic Member
Re: Permissions to use SPs and UDFs that don't update data
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|