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 13, 2012 at 11:24 am

    #262817

    I have a certain database in a SQL instance that serves as just a library of code (sprocs and UDFs) and needs to be freely accessed from code on other databases in the same instance.

    This is a generic library that needs to be made accessible to all logins on the server.

    Say userA is executing sprocA on databaseA and within sprocA there is a reference to a sproc in the library db.

    userA should be able to execute that portion of the code - the portion referencing the library-db object.

    How can a set up this access without having to explicitly grant EXEC permissions on each sproc/UDF in the library db to specific logins/groups?

    I tried to add the public role to db_owner in library db but it gave me an error.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • January 13, 2012 at 11:29 am

    #1433435

    Create a group called something like "LibraryExecutors" (sounds bad, you might think of a better name), put everyone in it, grant that group execute rights in your library database. Not db_owner, use Grant Execute.

    Something like:

    CREATE ROLE [db_executor];

    grant execute to db_executor;

    EXEC sp_addrolemember N'db_executor', N'MyNewGroup';

    - 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 13, 2012 at 11:34 am

    #1433439

    Althout that solution will work, it still would require one to add every user created (even in the future) to that group. Is there any way to allow all users to access the "library" db without specifically adding priviliges for each and every user?

  • January 13, 2012 at 11:46 am

    #1433444

    You could simplify to:

    GRANT EXEC TO PUBLIC;

    I'm not sure I'd go that far in any database I'm responsible for, but it should work.

    - 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 13, 2012 at 11:48 am

    #1433445

    One of the way to do it is to enable the guest user :

    Use Your_db

    go

    grant connect to guest

    grant execute to guest

    go

  • January 13, 2012 at 11:50 am

    #1433449

    GSquared (1/13/2012)

    You could simplify to:

    GRANT EXEC TO PUBLIC;

    I think you still have to create a user in the database with that.

  • January 13, 2012 at 11:52 am

    #1433450

    azdzn (1/13/2012)

    GSquared (1/13/2012)

    You could simplify to:

    GRANT EXEC TO PUBLIC;

    I think you still have to create a user in the database with that.

    Use the guest account.

    Edit: Just noticed you suggested about the same thing. Either method works.

    - 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 13, 2012 at 2:00 pm

    #1433515

    azdzn (1/13/2012)

    One of the way to do it is to enable the guest user :

    Use Your_db

    go

    grant connect to guest

    grant execute to guest

    go

    Thank you all for the input, it is appreciated.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • January 16, 2012 at 12:01 pm

    #1434219

    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

  • January 16, 2012 at 9:28 pm

    #1434390

    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.

    --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 6:20 am

    #1434558

    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.

    - 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:35 am

    #1434566

    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.

    --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 6:38 am

    #1434569

    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.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • January 17, 2012 at 7:48 am

    #1434615

    No, public did not work without creating a user in the target db first, which is what I am trying to avoid.

  • January 17, 2012 at 1:33 pm

    #1434843

    rlevine (1/17/2012)

    No, public did not work without creating a user in the target db first, which is what I am trying to avoid.

    Ok... thanks for the feedback.

    --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

Viewing 15 posts - 1 through 15 (of 19 total)

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