Minimum permission for user to grant execute on a stored procedure they created

  • We have created a schema xyz on a database to enable developers to create and run stored procedures for reporting purposes on a specific database. They have db_datareader rights on the database

    The AD group of which the developers are members have the following grants on the schema on that database

    GRANT EXECUTE ON SCHEMA::[xyz ] TO [ADGroup] WITH GRANT OPTION

    GRANT ALTER ON SCHEMA::[ xyz ] TO [ADGroup]

    GRANT VIEW DEFINITION ON SCHEMA::[ xyz ] TO [ADGroup]

    GRANT CREATE PROCEDURE TO [xyz ]

    Together with grant create procedure rights

    The developers can create/alter procedures OK, however when they try to assign the execute permissions on a proceedure they have created to a local SQL service account on the server

    e.g.

    Grant execute on SchemaName.SPName to LocalSQLaccount is fails with a message about insufficient permissions.

    We don’t want the developers to be able to create or change anything outside of their schema, but do want them to be able to assign execute rights to this service account for any new procedures they create.

    As the DBA I can assign execute rights to the procedure OK.

    What are the minimum permissions the developer’s AD group need to successfully grant execute to the service account?

  • IMHO, if this is on a production box, you've given the Developers way too many privs to begin with.  Who is doing the QA/UAT on the reports before they're "officially" used?  Who is reviewing the code for safety, performance, correct answers, access, etc?

    And why are Developers being allowed to grant access to anyone?

    You're setting yourself up for a massive and sudden career change that you won't enjoy.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Barring the questions @JeffModen has - why not just grant execute to the local service account one time and be done?

    GRANT EXECUTE ON schema::xyz TO [localSQLAccount];

    Any new procedures created - or procedures dropped and recreated - will still be accessible because the grant is on the schema and not the specific procedure(s).  That would allow you to remove the GRANT OPTION from the individual developers - as they would no longer need to grant any access to the local SQL account.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • with your permission set, if I grant that to a user, I get this error for the grant for a procedure:

    Msg 15151, Level 16, State 1, Line 14
    Cannot find the object 'xxx', because it does not exist or you do not have permission.

    That leads me to believe the issue is not with the GRANT, but with some sort of visibility of the proc.

    However, as Jeffrey noted, this limited user can do a

    GRANT EXECUTE ON schema::[xyz] TO [ADGroup]

    Which works fine for the ADGroup user

  • Try this:

    GRANT EXECUTE ON xyz.SPName TO LocalSQLaccount AS ADGroup;

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • None of this will ever pass an audit if this is in a prod or even a test system.  Good luck!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • to be able to grant others permission you need to be assigned the GRANT WITH GRANT option, this defaults if you own the object

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • GRANT with GRANT was listed at the top. This permission was given.

  • so it was, apologies, did not see that in the line of code.

    Best to mention it anyway

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • To grant permissions on a stored procedure

     

    Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role,

    https://forpc.onl/bitlife-life-simulator-on-pc/

    • This reply was modified 2 days, 3 hours ago by  warlordone.
  • Heh... let's talk maximum permissions for Developers in prod and test environments... NONE! This isn't to deprive Developers of anything cool... it's to protect them from being blamed for every blasted thing that could possibly go wrong.  I used to be a Developer and I know that even if you toe the line to the "T", you will be the first suspect and there's little that can be done to avoid it.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I go the opposite way.  In development / QA / etc. (i.e., not prod!), I give developers most permissions.  The only thing I prevent -- when I'm allowed to -- is CREATE INDEX or ADD CONSTRAINT that creates an index.  Nearly all developers think they know how indexes should be created and defined.  They simply do not.

    I'd also much prefer they get with me prior to creating tables and views, but, sadly I can't really enforce that here.

    As to T-SQL code, most get with me to check performance for their approach.  If they don't, that's on them.

    There's people developing all over the world.  They can't be held up.  And most developers are so disorganized they can't get arrange ahead of time to walk thing thru with a DBA (nor, often, do they want to).

    Btw, I'm not trying to interfere in their general coding approach, etc..  My only focus is to make sure the data design, and T-SQL and other SQL Server-related things are correct and efficient.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • That's not quite the opposite way for me... I give the Developers SysAdmin privs in the Dev box (and the system login is limited to only Dev Access).  Then, I do my damnedest to keep other people out of the Dev box because that's where the Developers get to try just about anything they want or can imagine.  I treat it just like the prod machine except with more copies of the databases and I, yeah, I do PIT backups because it's the nature of such a box to expect a mistake that might need a restore.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @scottpletcher & @JeffModen:

    You both are assuming an environment where there is a dev/test/uat/prod set up.  Not saying the OP doesn't have that - but there are environments where that isn't the case, especially when looking at BI environments (ie data warehouse/reporting).  In those environments it is typical to create a 'reporting' database where 'developers' (e.g. business analysts) write queries to support reporting requirements.

    In those types of environments - I find setting up a separate database for the 'developers' where they can create what is needed to support reporting while granting read access to the 'production' data is the better option.  Of course, on my systems the 'production' data is a read-only copy available either through a read-only secondary or through a backup/restore process.

    In a true development environment - then I agree, developers would never have access to production and more or less open access to a development environment.  And a deployment process that moves changes into a higher environment with code review and sign-off prior to approving the move.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • This helps

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

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