Technical Article

Permissions Crosstab

,

This procedure takes the results of sp_helprotect and converts it into a crosstab -- all permissions for a user/table combination are listed in a single row

---------------------------------------------------------------------
-- name:	sp_PermissionsCrossTab
-- auth:	Sal Terillo
-- email:	sal.terillo@intrasphere.com
-- date:	4/9/2002
--  		Returns database permissions on a user/object basis
--		in a single row
-- usage:	Returns User specific permissions if a valid user 
--		name is entered.. otherwise returns permissions
--		for all users in DB
---------------------------------------------------------------------
CREATE PROCEDURE sp_PermissionsCrossTab @UserName SYSNAME = NULL

AS

SET NOCOUNT ON

DECLARE @SQLStatement VARCHAR(3000)
DECLARE @Action VARCHAR(400)
DECLARE @DynamicSQL VARCHAR(2000)

-- temp table holds output of sp_helprotect sproc
CREATE TABLE #HelpProtect
	(Owner sysname, 
	 ObjectName sysname, 
	 Grantee sysname,
	 Grantor sysname, 
	 ProtectType VARCHAR(200),
	 Action VARCHAR(100),
	 ColumnName SYSNAME NULL)

-- insert the results
-- of sp_Helprotect into temp table
INSERT #HelpProtect
EXEC sp_helprotect

-- let's get a list of possible
-- permissions
DECLARE  cur_Action cursor FOR
select distinct [action] from #helpProtect
ORDER BY Action

OPEN cur_Action

-- populate cursor
FETCH NEXT FROM cur_Action INTO @Action
-- strip out spaces
SET @Action = REPLACE(@Action, ' ', '')

-- start building dynamic SQL statement
SET @SQLStatement = 'SELECT Grantee UserName, ObjectName, ' + CHAR(10)

-- initialize
SET @DynamicSQL = ''

WHILE @@FETCH_STATUS = 0
   BEGIN
	SET @DynamicSQL = @DynamicSQL + 'MAX(CASE WHEN Action = ''' + @Action + '''' + ' THEN 1 ELSE 0 END) AS [' + @Action + '],' + CHAR(10)
	FETCH NEXT FROM cur_Action INTO @Action	
	SET @Action = REPLACE(@Action, ' ', '')
   END
	-- trim carriage return and last comma
	SET @DynamicSQL = LEFT(@DynamicSQL, DATALENGTH(@DynamicSQL) - 2)

	-- start building the final statement
	SELECT @SQLStatement = @SQLStatement + @DynamicSQL
	SELECT @SqlStatement = @SQLStatement + CHAR(10) + 'FROM #HelpProtect' + CHAR(10)

	-- if a user name is supplied we add that to the statement
	IF @UserName IS NOT NULL AND EXISTS(SELECT * FROM sysusers WHERE name = @UserName)
	   BEGIN
		SELECT @SQLStatement = @SQLStatement + 'WHERE Grantee = ''' + @UserName + '''' + CHAR(10)
	   END

	-- add the group by section
	SELECT @SQLStatement = @SQLStatement + 'GROUP BY GRANTEE, ObjectName' + CHAR(10)

	-- add the order by		 
	SELECT @SqlStatement = @SQLStatement + CHAR(10) + 'ORDER BY GRANTEE, Objectname'
	EXEC (@SQLStatement)

-- cleanup
CLOSE cur_Action
DEALLOCATE cur_Action

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating