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

Question

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

December 18, 2021 at 8:17 am

This was removed by the editor as SPAM
December 18, 2021 at 8:18 am

This was removed by the editor as SPAM
May 4, 2022 at 10:48 am

This was removed by the editor as SPAM

Viewing 6 posts - 16 through 20 (of 20 total)

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

ScottPletcher SSC Guru Points: 100951 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

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

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

ScottPletcher SSC Guru Points: 100951 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.