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

  • I don't see a huge security issue with a separate schema.  Thus, I don't see why this would necessarily fail an audit.  That seems to be an overly broad claim.

    I have separate dbs that specific power owners have full read, write and ddladmin to (but no db level permissions, i.e., they can't directly backup the db, drop the db, add users, etc.), and we pass an audit every year.

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

  • Jeffrey Williams wrote:

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

    That's fine and dandy and totally true and a couple of other nice terms but, it actually doesn't have anything to do with what I'm talking about.  Even in such an environment as you speak, why do Developers or End Users need to have enough privs to grant access to others for stored procedures and the like?  They don't.  There still needs to be a central authority that determines the viability of the code, whether it meats requirements not, and controls when something is "released" for access and by whom .

    --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 think a separate schema(s) is a valid approach.  Indeed, it has some advantages.  For one, the developer-created objects are in the db to which they apply.  If you create only a single db, a "developer db", with all objects, then objects in that db could point to any of the other dbs (hundreds of them, in our case).  If that db then needs to move somewhere [cloud, new instance, wherever], for whatever reason, it can very difficult to find all linking objects if they're not already in the db to be moved.

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

Viewing 3 posts - 16 through 18 (of 18 total)

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