Post reply

Need to grant execute permissions on all sprocs of a specific database to all server logins of a SQL instance without explicitly granting exec permissions on each individual object

  • January 17, 2012 at 1:34 pm

    #1434844

    Marios Philippopoulos (1/17/2012)

    Even "best practices" should be looked at with a critical eye.

    Precisely why I encapsulated "best practices" in quotes. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 17, 2012 at 1:36 pm

    #1434845

    Jeff Moden (1/17/2012)

    GSquared (1/17/2012)

    Jeff Moden (1/16/2012)

    rlevine (1/16/2012)

    Perfect!!

    I was able to install my CLR functions into a db called Library and then use these scripts to enable having all users call the functions without needing to create a new user on the Library dB for all of my users.

    use Library;

    go

    grant connect to guest

    grant execute to guest

    grant select to guest

    go

    PUBLIC didn't work? I ask only because one of the usual "best practices" for security reasons is to disable the GUEST account.

    Public is a role, not an account. You'd have to make sure everyone was in that role, and apparently that's not an option here, per a prior post.

    This whole thing is going into realms, security-wise, that I'd NEVER allow on any server I'm responsible for, but apparently it's what's needed here.

    Yep... I know Public is a role and, by default (IIRC), everyone is a member of that role at creation time.

    Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 17, 2012 at 1:37 pm

    #1434846

    Marios Philippopoulos (1/17/2012)

    GSquared (1/17/2012)

    Jeff Moden (1/16/2012)

    rlevine (1/16/2012)

    Perfect!!

    I was able to install my CLR functions into a db called Library and then use these scripts to enable having all users call the functions without needing to create a new user on the Library dB for all of my users.

    use Library;

    go

    grant connect to guest

    grant execute to guest

    grant select to guest

    go

    PUBLIC didn't work? I ask only because one of the usual "best practices" for security reasons is to disable the GUEST account.

    Public is a role, not an account. You'd have to make sure everyone was in that role, and apparently that's not an option here, per a prior post.

    This whole thing is going into realms, security-wise, that I'd NEVER allow on any server I'm responsible for, but apparently it's what's needed here.

    Depends what you give access on. If, for example, the Library db contains only UDFs for regular expressions, then the risk of harm by allowing guest access on these objects is minimal. It's a balance between what is safe to allow and what will give the most savings in terms of future maintenance.

    Even "best practices" should be looked at with a critical eye.

    I agree that it's probably safe now. The problem is scope-creep on things like this. Are you familiar with the parable about how the camel got into the tent?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 17, 2012 at 6:08 pm

    #1434935

    GSquared (1/17/2012)

    Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.

    Ah... sorry. I misunderstood the original problem.

    Considering how easy it is to have the system generate a script to grant access to databases, I wouldn't simply use the "Guest" account. There's no tracability if everyone uses the "Guest" account. Yes, I also realize that's the way that most applications also work but you know what's next... I'm against that, as well. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 18, 2012 at 6:12 am

    #1435187

    Jeff Moden (1/17/2012)

    GSquared (1/17/2012)

    Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.

    Ah... sorry. I misunderstood the original problem.

    Considering how easy it is to have the system generate a script to grant access to databases, I wouldn't simply use the "Guest" account. There's no tracability if everyone uses the "Guest" account. Yes, I also realize that's the way that most applications also work but you know what's next... I'm against that, as well. 😉

    Yep. Which is why I consider this whole plan risky. If it's worth it for the reward in this case, that's not something I can judge, but it does have liabilities that have been pointed out.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply