To filter store procedures that only do select operation in a particular Database

[harishpinisetti]

Hi,
Do you know is there any procedure that we can find store procedures in a database that only do select operation rather than alter,update are delete operation and can use a cursor from the results to grant exec permissions to a particular login group on those selected stored procedures.

Thanks,
Harish.

[ck9663]

Hi,
Do you know is there any procedure that we can find store procedures in a database that only do select operation rather than alter,update are delete operation and can use a cursor from the results to grant exec permissions to a particular login group on those selected stored procedures.

Thanks,
Harish.

what about trigger on tables?

-- ck

[Delerna]

Do you mean something like this

SELECT ListProcs.[Name]
from
(select b.name
from syscomments a
join sysobjects b on a.ID=b.ID
where text like '%Create Proc%') ListProcs
join
(select b.name
from syscomments a
join sysobjects b on a.ID=b.ID
where text like '%SELECT%') ListSelects on ListProcs.[Name]=ListSelects.[Name]
left join
(select b.name
from syscomments a
join sysobjects b on a.ID=b.ID
where text like '%INSERT%') ListInserts  on ListProcs.[Name]=ListInserts.[Name]
where ListInserts.[Name] is null
 

this query will list all stored procedures that contain the string 'SELECT' and also does not contain the string 'INSERT'

Run it in query analyser and adjust it to suit your needs