How to Grant Execute to all Functions to one user

Question

How to Grant Execute to all Functions to one user

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
December 4, 2009 at 12:36 pm

#137471

Is there an easy way to grant execute to all functions rather than creating a script for one user for each function?
Thanks in advance for your help!:-D
¤ §unshine ¤

Viewing 11 posts - 1 through 10 (of 10 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Create a cursor that selects all the functions from the system views, have it step through them and build a dynamic SQL command to grant the permissions.

- 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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

alternatively, create a script that will concatenate the statement in a set based fashion

select 'grant execute on ['+ name + '] to sa'

from sys.objects

where type = 'fn'

and is_ms_shipped = 0

Copy the results to a new query window, evaluate them, and then execute them.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Taking Jason's fine code to the next level so that you don't have so much manual interaction (and, you could turn this into a DBA stored proc)...

DECLARE @sql VARCHAR(MAX)

SELECT @sql = (SELECT 'GRANT EXECUTE ON ['+ name + '] TO sa' +CHAR(10)

FROM sys.objects

WHERE TYPE = 'fn'

AND is_ms_shipped = 0

FOR XML PATH(''))

--PRINT @sql

EXEC (@SQL)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

Please don't use "sa" in examples. It sends a bad message, even though it doesn't matter in this example.

However, the way you want to do this, using the code above, is grant these rights to a ROLE, not a user. Add users to the role, don't grant rights to each user.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Nice catch Steve.

You will need to replace 'sa' with the Role for which you wish to grant access. As Steve stated, you should grant permissions to a Role and not directly to the user. It is easier maintenance and better practice to use Roles for permissions assignment than directly to the users.

Using Jeff's version, the code would be:

DECLARE @sql VARCHAR(MAX)

SELECT @sql = (SELECT 'GRANT EXECUTE ON ['+ name + '] TO YourRole' +CHAR(10)

FROM sys.objects

WHERE TYPE = 'fn'

AND is_ms_shipped = 0

FOR XML PATH(''))

--PRINT @sql

EXEC (@SQL)

I do have to add another sidebar on this though. It isn't always necessary to grant execute to all Functions or Procs. It is better to find out what exactly is needed for the Role and then assign explicitly those perms to that user.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Steve Jones - Editor (12/5/2009)
Please don't use "sa" in examples. It sends a bad message, even though it doesn't matter in this example.
However, the way you want to do this, using the code above, is grant these rights to a ROLE, not a user. Add users to the role, don't grant rights to each user.

You're right, of course. :blush: I was just being incredibly lazy. For a Saturday, it's been an incredibly long "code day". :hehe:

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

GSquared SSC Guru Points: 260824 More actions · Answer 7

The reason I use cursors for this kind of thing, instead of "quirky update" string building, is that I can wrap each command in error handling much more easily.

- 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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

And, depending on your design, you could skip the whole cursor by granting execute privileges to a particular schema:

GRANT EXECUTE ON someschema TO somerole

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

GSquared (12/7/2009)
The reason I use cursors for this kind of thing, instead of "quirky update" string building, is that I can wrap each command in error handling much more easily.

You can also do that with the string building method fairly easily... but I agree... for something like this, a cursor isn't going to slow anything down and it's one of the few places where I won't bite someone's head off for using a cursor. I just can't bring myself to actually write a cursor... :-P:hehe:

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

GSquared (12/7/2009)
The reason I use cursors for this kind of thing, instead of "quirky update" string building, is that I can wrap each command in error handling much more easily.

This is one of those places where it wouldn't hurt too much to use a cursor - and is common practice. I just wanted to provide an alternate method.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events