CREATE     PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	(	
		(type = 'P' AND name LIKE 'usp[_]%') 
		OR
		(type = 'FN' AND name LIKE 'udf[_]%')
		OR
		(type = 'TF' AND name LIKE 'udf[_]%')
		OR
		(type = 'U')
		OR
		(type = 'V' AND name LIKE 'v[_]%')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType NOT IN ('TF', 'U', 'V')
	BEGIN
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO SomeRole')
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO SomeRole')
	END

	ELSE
	BEGIN
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO SomeRole')	
	END

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO