show which groups have permissions on which stored procedures

[voxinforma]

What's the best way to get a list of which user groups have permission to execute which stored procs?

Thanks.

 

[carrr]

Try using (wihtin a given database):

exec sp_helprotect

This returns grantee names and action types.

Hope this helps.


carrr

 

[tlbroadbent]

Sometimes, I prefer to create my own procedure to get info such as this because the built in system SPs provide too much info. The following script returns all SP execute permissions for a database.

--Initialize
set nocount on
set ansi_warnings off

--Create temp table for intermediate results
Create Table #protects
(Owner varchar(12), Object varchar(60),
Grantee varchar(20), Grantor varchar(12),
Type varchar(12),
[Action] varchar(12), [Column] varchar(30))

--Insert the output of sp_helprotect into temp table
Insert #protects
(Owner, Object, Grantee,
Grantor, Type,
[Action], [Column])
exec travel.dbo.sp_helprotect

--Select columns I want to see
--from rows with 'execute' permission
Select Owner, Object, Grantee, Type, [Action]
From #protects
Where [Action]='Execute'
Order By Object, Grantee, Type

--clean up
drop table #protects
set ansi_warnings on
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.