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 Tuesday, December 13, 2005 11:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 9:49 AM
Points: 44, Visits: 42

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.

Post #243888
Posted Tuesday, December 13, 2005 11:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:52 PM
Points: 290, Visits: 52
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
Post #243897
Posted Tuesday, December 13, 2005 7:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739

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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #244023
Posted Wednesday, December 13, 2006 5:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

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.

Post #330079
Posted Wednesday, December 13, 2006 6:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:56 AM
Points: 210, Visits: 118

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)

 

 




Post #330095
Posted Sunday, March 25, 2007 6:57 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:44 AM
Points: 666, Visits: 58

 

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.

 

 

Post #353745
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse