Grant Execute Permission on SPs

declare @rows intdeclare @inx int

declare @tempProcs TABLE (rowID int NOT NULL, rowName varchar(250) NOT NULL) declare @tempProcName varchar(250)declare @query varchar(250)

Set @rows = (select count(*) from sysobjects where xtype = 'P' and name not like 'SP_%' and name not like 'dt_%')

insert into @tempProcsselect counter = 0, name from sysobjects where xtype = 'P' and name not like 'SP_%' and name not like 'dt_%'

--select * from @tempProcs
DECLARE @counter int

SET @counter = UPDATE @tempProcsSET @counter = rowID = @counter + 1

--select * from @tempProcs
Set @inx = (select Top 1 rowID from @tempProcs)
While (@inx <= @rows)

Begin

Set @tempProcName = (SELECT Top 1 rowName FROM @tempProcs where rowID >= @inx)-

-print @tempProcName

Set @inx = @inx + 1

Set @query = 'GRANT EXECUTE ON [dbo].['+@tempProcName+'] TO [userName]'-

-print @query

exec(@query)

--GRANT EXECUTE ON [dbo].[@tempProcName] TO [userName]

End