SQL Server Performance

permission grant on objects

Discussion in 'SQL Server 2005 General DBA Questions' started by reethu, May 1, 2007.

  1. reethu New Member

    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
  2. ndinakar Member

    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/
  3. satya Moderator

    Why cant you take help of SP_HELPROTEcT in this case to get permission information on an object.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page