Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    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?
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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

  3. #3
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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.

  4. #4

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    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.
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

  5. #5
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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

  6. #6

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    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.
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width