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

Gareth

SSC Enthusiast

Points: 172
More actions
May 4, 2021 at 2:40 pm

#3879190

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?

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

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

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.

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 4

Try this:

GRANT EXECUTE ON xyz.SPName TO LocalSQLaccount AS ADGroup;

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: 1003865 More actions · Answer 5

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.

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

Perry Whittle SSC Guru Points: 233865 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 7

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

Perry Whittle SSC Guru Points: 233865 More actions · Answer 8

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

warlordone Grasshopper Points: 12 More actions · Answer 9

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 years, 11 months ago by warlordone.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

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.

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 12

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.

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 13

@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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

warlordone Grasshopper Points: 12 More actions · Answer 14

This helps

nox player for pc[/url] jiofi.local.html

This reply was modified 2 years, 11 months ago by warlordone.