Audit script for User/Group permissions on a datab

/* SQL SCRIPT TO AUDIT THE PERMISSIONS FOR OBJECTS IN A DATABASE.
AUTHOR: JAGDEEP SAHDEVA

ACTION CAN HAVE ONE OF THESE PERMISSIONS: 
26  = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE

OBJECT TYPE
C = CHECK CONSTRAINT
D = DEFAULT OR DEFAULT CONSTRAINT
F = FOREIGN KEY CONSTRAINT
L = LOG
FN = SCALAR FUNCTION
IF = INLINED TABLE-FUNCTION
P = STORED PROCEDURE
PK = PRIMARY KEY CONSTRAINT (TYPE IS K)
RF = REPLICATION FILTER STORED PROCEDURE 
S = SYSTEM TABLE
TF = TABLE FUNCTION
TR = TRIGGER
U = USER TABLE
UQ = UNIQUE CONSTRAINT (TYPE IS K)
V = VIEW
X = EXTENDED STORED PROCEDURE

PROTECTTYPE CAN HAVE THESE VALUES: 
204 = GRANT_W_GRANT
205 = GRANT
206 = REVOKE


*/

DECLARE @REPORT TABLE
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
      ,OBJECT VARCHAR(100)
,OBJECT_TYPE VARCHAR(32)
,OWNER VARCHAR(32)
,SQLUSER VARCHAR(32)
,PERMISSION VARCHAR(32)
,PROTECTTYPE VARCHAR(32)
,CREATE_DATE DATETIME)



INSERT INTO @REPORT 
SELECT NAME
,'OBJECT TYPE' = CASE O.XTYPE  
WHEN 'C' THEN  'CHECK CONSTRAINT'
WHEN 'D' THEN  'DEFAULT OR DEFAULT CONSTRAINT'
WHEN 'F' THEN  'FOREIGN KEY CONSTRAINT'
WHEN 'L' THEN  'LOG'
WHEN 'FN' THEN 'SCALAR FUNCTION'
WHEN 'IF' THEN 'INLINED TABLE-FUNCTION'
WHEN 'P' THEN  'STORED PROCEDURE'
WHEN 'PK' THEN 'PRIMARY KEY CONSTRAINT (TYPE IS K)'
WHEN 'RF' THEN 'REPLICATION FILTER STORED PROCEDURE'
WHEN 'S' THEN  'SYSTEM TABLE'
WHEN 'TF' THEN 'TABLE FUNCTION'
WHEN 'TR' THEN 'TRIGGER'
WHEN 'U' THEN  'USER TABLE'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT (TYPE IS K)'
WHEN 'V' THEN  'VIEW'
WHEN 'X' THEN  'EXTENDED STORED PROCEDURE'
END
, OWNER = USER_NAME(O.UID)
,SQLUSER = NULL
,NULL
,NULL
, CRDATE
FROM DBO.SYSOBJECTS O 
WHERE POWER(2, SYSSTAT & 0XF) & 31 != 0 
AND NOT (OBJECTPROPERTY(O.ID, N'ISDEFAULTCNST') = 1 
AND CATEGORY & 0X0800 != 0) 
--AND O.NAME NOT LIKE N'#%'


SELECT 
 ID    
,R.OBJECT 
,R.OBJECT_TYPE
,R.OWNER 
,PO.SQLUSER 
,PO.PERMISSION 
,PO.PROTECTTYPE 
,R.CREATE_DATE  

FROM @REPORT R LEFT OUTER JOIN  
(SELECT OBJECT = O.NAME
,OBJECT_TYPE = CASE O.XTYPE  
WHEN 'C' THEN  'CHECK CONSTRAINT'
WHEN 'D' THEN  'DEFAULT OR DEFAULT CONSTRAINT'
WHEN 'F' THEN  'FOREIGN KEY CONSTRAINT'
WHEN 'L' THEN  'LOG'
WHEN 'FN' THEN 'SCALAR FUNCTION'
WHEN 'IF' THEN 'INLINED TABLE-FUNCTION'
WHEN 'P' THEN  'STORED PROCEDURE'
WHEN 'PK' THEN 'PRIMARY KEY CONSTRAINT (TYPE IS K)'
WHEN 'RF' THEN 'REPLICATION FILTER STORED PROCEDURE'
WHEN 'S' THEN  'SYSTEM TABLE'
WHEN 'TF' THEN 'TABLE FUNCTION'
WHEN 'TR' THEN 'TRIGGER'
WHEN 'U' THEN  'USER TABLE'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT (TYPE IS K)'
WHEN 'V' THEN  'VIEW'
WHEN 'X' THEN  'EXTENDED STORED PROCEDURE'
END
, OWNER = USER_NAME(O.UID)
, SQLUSER = USER_NAME(P.UID)
,  PERMISSION = CASE ACTION
WHEN 26  THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END
, PROTECTTYPE =CASE P.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'REVOKE'
END

FROM DBO.SYSPROTECTS P, DBO.SYSOBJECTS O,     MASTER.DBO.SPT_VALUES A 

WHERE O.ID = P.ID 
AND (CONVERT(TINYINT, SUBSTRING( ISNULL(P.COLUMNS, 0X01), A.LOW, 1)) & A.HIGH != 0) 
)AS PO

ON R.OBJECT = PO.OBJECT
ORDER BY PO.PERMISSION DESC