Granting Execute Access to All Stored Procedures to a Given User

Question

Post reply

Granting Execute Access to All Stored Procedures to a Given User

Tim Mitchell

SSCoach

Points: 15685
More actions
November 21, 2005 at 4:56 pm

#169792

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tmitchell/grantingexecuteaccesstoallstoredprocedurestoagiven.asp
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

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

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

David.Poole SSC Guru Points: 76113 More actions · Answer 1

This might work a bit faster

DECLARE @ProcName sysname ,

@usercredentials sysname

set @procname=''

set @usercredentials='Development'

WHILE @procname is not null

begin

SELECT @procname = min(name)

FROM sysobjects

WHERE xtype='P' and name > @procname and name not like 'dt_%'

IF @procname is not null

BEGIN

EXEC('GRANT EXECUTE ON ' + @procname+ ' TO ' + @usercredentials)

PRINT 'Execute permissions granted on '+ @procname+ ' TO ' + @usercredentials

END

LinkedIn Profile

Adam Cruickshank Grasshopper Points: 13 More actions · Answer 2

Whatever happened to using a good old fashioned cursor?

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

Just to add that it's often much easier in multi-user environments where more than a couple of users need similar access to create a single role and grant permissions to the role, then you can just add the users to the role to save you time.

F Vandeputte Old Hand Points: 356 More actions · Answer 4

The code below will do the trick with only one line of code:

EXEC master..xp_execresultset

N'SELECT ''GRANT EXEC ON '' + o.name + '' TO '' + ''my_user''

FROM sysobjects o

WHERE xtype = ''P'' AND [name] NOT LIKE ''dt_%'' ', my_db

Just replace my_user and my_db with the correct values.

I posted some more nice examples on xp_execresultset my blog some time ago:

http://www.vandeputte.org/2005/10/xpexecresultset.html

Kind regards,

Frederik

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 5

Yes I'm being picky but doesn't this belong in Scripts rather than articles?

Terry

David.Poole SSC Guru Points: 76113 More actions · Answer 6

"Good" and "cursor" in the same sentence.

"Old Fashioned" is OK, it implies that something has worked

LinkedIn Profile

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 7

I can't agree more, there's no way that those 2 words should ever appear in the same sentence

Oh, wait, how about...

"it's no good, he's used a cursor rather than doing it properly with set based logic"

or

"good, said the highly paid consultant, i see that they've used a cursor; i should be able to make things a little faster if you want to pay me some more money"

(but only for 99.99999 % of cases)

CDJorg Old Hand Points: 342 More actions · Answer 8

This likely won't make any difference when looping through stored procedure names, but it should be noted that "_" is a wildcard, representing any one character.

like 'dt_%' would be better written as like 'dt\_%' escape '\' to force the script to treat the underscore as itself.

Otherwise, great script! Very useful.

dlhatheway@mmm.com Old Hand Points: 382 More actions · Answer 9

I'd vote for creating the "executors" role, adding "execute" permission on all the SPs to that and then adding the user(s) in question to the "executors" role.

Maybe "executors" would be a good addition to the model db.

By the way, where's your defensive "is this a real login" check? Chalk it up to paranoia, if you like, but I'd have a test in there.

Ted Crafton-214367 SSC Journeyman Points: 88 More actions · Answer 10

Everyone is down on Cusors and then they turn right around and use sysobjects in their scripts.

Why not use the views Microsoft provided?

SELECT specific_name FROM information_schema.routines where NOT specific_name like ('dt_%')

I mean if you are going to do it the proper way, then do it all by the book.

John Scarborough Ten Centuries Points: 1104 More actions · Answer 11

We had a situation a while back in which a cursor based solution was faster than a t-sql solution. Note that this was NOT what we were attempting to prove to the developer! Oh well.

John Scarborough
MCDBA, MCSA

Tim Mitchell SSCoach Points: 15685 More actions · Answer 12

Cursors are like toilet plungers: you hope you don't have to use them, but it's nice to know how just in case.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 13

I looked up the bol. What min(name) here do ?

SELECT @procname = min(name)

FROM sysobjects

WHERE xtype='P' and name > @procname and name not like 'dt_%'

Thanks.

robertm Ten Centuries Points: 1096 More actions · Answer 14

If you wanted to avoid usiing extended stored prcedures you could do it like this. It's a bit more understandable than multiple nested select statements that are required for xp_execresultset to work properly...

-- Specify the user account which should be

DECLARE @login VARCHAR(50)

SET @login = 'my_user_id'

DECLARE @sql VARCHAR(max)

Select

@sql = Coalesce(@sql, '') + 'GRANT EXEC ON [' + [name] + '] TO [' + @login + ']' + Char(13)

FROM sysobjects WHERE xtype = 'P' AND [name] NOT LIKE 'dt_%'

EXEC (@sql)

Granting Execute Access to All Stored Procedures to a Given User

Cookies on SQLServerCentral