SQLServerCentral / Discuss Content Posted by Tim Mitchell / Article Discussions / Article Discussions by Author / Granting Execute Access to All Stored Procedures to a Given User / Latest Posts

RE: Granting Execute Access to All Stored Procedures to a Given User

Brian Martin — Sun, 25 Mar 2007 18:57:00 GMT

To grant execute permissions to a user for all stored procs in a schema. Using the GUI

Right click a stored proc.

Click properties

Click view schema permissions

Add

Object type -> user -> ok

Brows

Choose user -> ok

Click on the user

Under grant check execute.

RE: Granting Execute Access to All Stored Procedures to a Given User

robertm — Wed, 13 Dec 2006 06:37:00 GMT

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)

RE: Granting Execute Access to All Stored Procedures to a Given User

Frances L — Wed, 13 Dec 2006 05:42:00 GMT

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

SELECT @procname = min(name)FROM sysobjectsWHERE xtype='P' and name > @procname and name not like 'dt_%'

Thanks.

RE: Granting Execute Access to All Stored Procedures to a Given User

Tim Mitchell — Tue, 13 Dec 2005 19:03:00 GMT

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

RE: Granting Execute Access to All Stored Procedures to a Given User

John Scarborough — Tue, 13 Dec 2005 11:46:00 GMT

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.

RE: Granting Execute Access to All Stored Procedures to a Given User

Ted Crafton-214367 — Tue, 13 Dec 2005 11:14:00 GMT

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.

RE: Granting Execute Access to All Stored Procedures to a Given User

dlhatheway@mmm.com — Tue, 13 Dec 2005 08:50:00 GMT

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.

RE: Granting Execute Access to All Stored Procedures to a Given User

CDJorg — Tue, 13 Dec 2005 08:31:00 GMT

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.

RE: Granting Execute Access to All Stored Procedures to a Given User

Mike Metcalf-160221 — Tue, 13 Dec 2005 07:35:00 GMT

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"

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

RE: Granting Execute Access to All Stored Procedures to a Given User

David.Poole — Tue, 13 Dec 2005 07:22:00 GMT

"Good" and "cursor" in the same sentence."Old Fashioned" is OK, it implies that something has worked

RE: Granting Execute Access to All Stored Procedures to a Given User

TDuffy — Tue, 13 Dec 2005 06:29:00 GMT

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

Terry

RE: Granting Execute Access to All Stored Procedures to a Given User

F Vandeputte — Tue, 13 Dec 2005 04:10:00 GMT

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_dbJust 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.htmlKind regards,Frederik

RE: Granting Execute Access to All Stored Procedures to a Given User

Mike Metcalf-160221 — Tue, 13 Dec 2005 02:32:00 GMT

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.

RE: Granting Execute Access to All Stored Procedures to a Given User

Adam Cruickshank — Tue, 13 Dec 2005 02:31:00 GMT

Whatever happened to using a good old fashioned cursor?

RE: Granting Execute Access to All Stored Procedures to a Given User

David.Poole — Tue, 13 Dec 2005 01:53:00 GMT

This might work a bit fasterDECLARE @ProcName sysname , @usercredentials sysnameset @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 END

Granting Execute Access to All Stored Procedures to a Given User

Tim Mitchell — Mon, 21 Nov 2005 16:56:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tmitchell/grantingexecuteaccesstoallstoredprocedurestoagiven.asp