permission grant on objects

Hi Guys, i am running below stored procedure to get permissions on objecrs
but my print command not showing grant exec to 'role why it is happening.

ALTER PROC [dbo].[sp_role_read_exec_group]
AS
BEGIN
SET NOCOUNT ON
DECLARE @name varchar(255),
@type char(1),
@sql varchar(255)
DECLARE userObjects CURSOR READ_ONLY FOR
SELECT name, type
FROM sysobjects
WHERE type IN ( 'FN','IF', 'U', 'V' )
OPEN userObjects
FETCH NEXT FROM userObjects INTO @name, @type
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'GRANT ' +
CASE @type
-- WHEN 'P' THEN 'EXEC'
WHEN 'U' THEN 'SELECT'
WHEN 'V' THEN 'SELECT'
WHEN 'FN' THEN 'EXEC'
WHEN 'IF' THEN 'EXEC'
END +
' ON ' + @name + ' TO role_read_exec_group'
EXEC (@sql)
PRINT (@SQL)
FETCH NEXT FROM userObjects INTO @name, @type
END
CLOSE userObjects
DEALLOCATE userObjects
END

GRANT SELECT ON jumbo TO role_read_exec_group
GRANT SELECT ON v_jumbo TO role_read_exec_group
error
Msg 217, Level 16, State 1, Procedure sp_role_read_exec_group, Line 28
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).'
here one small change i need to grant only perticular stored procedures
starts like ameri_get...
how can i insert that in this procedures.

SRJ2005

first, you dont need a cursor for this. Run this query with "Results to Text" mode in your Query Analyzer, get the script generated and compile it.


SELECT 'GRANT ' +
CASE type
-- WHEN 'P' THEN 'EXEC'
WHEN 'U' THEN 'SELECT'
WHEN 'V' THEN 'SELECT'
WHEN 'FN' THEN 'EXEC'
WHEN 'IF' THEN 'EXEC'
END +
' ON ' + name + ' TO role_read_exec_group' + char(10) + Char(13) + 'Go'
from sysobjects
WHERE type IN ( 'FN','IF', 'U', 'V' )


***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/