SQL Server Performance Forum – Threads Archive
permission grant on objects
Hi Guys, i am running below stored procedure to get permissions on objecrsbut 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/
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.
]]>