Permissions Crosstab

---------------------------------------------------------------------
-- 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