Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Granting Execute Access to All Stored Procedures to a Given User Expand / Collapse
Author
Message
Posted Monday, November 21, 2005 4:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tmitchell/grantingexecuteaccesstoallstoredprocedurestoagiven.asp



Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #238704
Posted Tuesday, December 13, 2005 1:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 1:30 AM
Points: 2,898, Visits: 1,795
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

END


LinkedIn Profile
Newbie on www.simple-talk.com
Post #243726
Posted Tuesday, December 13, 2005 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 29, 2009 4:40 AM
Points: 1, Visits: 4
Whatever happened to using a good old fashioned cursor?
Post #243733
Posted Tuesday, December 13, 2005 2:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53
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.
Post #243734
Posted Tuesday, December 13, 2005 4:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 1:27 AM
Points: 116, Visits: 59
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
Post #243751
Posted Tuesday, December 13, 2005 6:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57

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

Terry




Post #243782
Posted Tuesday, December 13, 2005 7:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 1:30 AM
Points: 2,898, Visits: 1,795
"Good" and "cursor" in the same sentence.

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


LinkedIn Profile
Newbie on www.simple-talk.com
Post #243799
Posted Tuesday, December 13, 2005 7:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

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)

Post #243804
Posted Tuesday, December 13, 2005 8:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 20, 2006 10:12 AM
Points: 78, Visits: 1

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.

Post #243829
Posted Tuesday, December 13, 2005 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 18, 2012 9:46 AM
Points: 320, Visits: 12

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.




Post #243840
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse