SCCM 2007 - SCCM Permissions Matrix

 

This SQL query pulls all SCCM permissions for each user and displays them in a matrix.  Put this into an SCCM Web report.  This should work for SQL 2000, SQL 2005 and higher.

SELECT
   UCP.UserName,
   SO.ObjectName,  
   MAX(CASE UCP.PermissionName WHEN 'Administer' THEN 'X' END) as [Administer],
   MAX(CASE UCP.PermissionName WHEN 'Advertise' THEN 'X' END) as [Advertise],
   MAX(CASE UCP.PermissionName WHEN 'Create' THEN 'X' END) as [Create],
   MAX(CASE UCP.PermissionName WHEN 'Create Task Sequence Media' THEN 'X' END) as [Create Task Sequence Media],
   MAX(CASE UCP.PermissionName WHEN 'Delegate Explicit Instance Rights' THEN 'X' END) as [Delegate Explicit Instance Rights],
   MAX(CASE UCP.PermissionName WHEN 'Delete' THEN 'X' END) as [Delete],
   MAX(CASE UCP.PermissionName WHEN 'Delete Resource' THEN 'X' END) as [Delete Resource],
   MAX(CASE UCP.PermissionName WHEN 'Distribute' THEN 'X' END) as [Distribute],
   MAX(CASE UCP.PermissionName WHEN 'Import Machine' THEN 'X' END) as [Import Machine],
   MAX(CASE UCP.PermissionName WHEN 'Manage AI' THEN 'X' END) as [Manage AI],
   MAX(CASE UCP.PermissionName WHEN 'Manage BMC' THEN 'X' END) as [Manage BMC],
   MAX(CASE UCP.PermissionName WHEN 'Manage Folder' THEN 'X' END) as [Manage Folder],
   MAX(CASE UCP.PermissionName WHEN 'Manage OSD Certificate' THEN 'X' END) as [Manage OSD Certificate],
   MAX(CASE UCP.PermissionName WHEN 'Manage SQL Commands' THEN 'X' END) as [Manage SQL Commands],
   MAX(CASE UCP.PermissionName WHEN 'Manage Status Filters' THEN 'X' END) as [Manage Status Filters],
   MAX(CASE UCP.PermissionName WHEN 'Meter Site' THEN 'X' END) as [Meter Site],
   MAX(CASE UCP.PermissionName WHEN 'Modify' THEN 'X' END) as [Modify],
   MAX(CASE UCP.PermissionName WHEN 'Modify Collection Setting' THEN 'X' END) as [Modify Collection Setting],
   MAX(CASE UCP.PermissionName WHEN 'Modify Resource' THEN 'X' END) as [Modify Resource],
   MAX(CASE UCP.PermissionName WHEN 'Network Access' THEN 'X' END) as [Network Access],
   MAX(CASE UCP.PermissionName WHEN 'Read' THEN 'X' END) as [Read],
   MAX(CASE UCP.PermissionName WHEN 'Read Resource' THEN 'X' END) as [Read Resource],
   MAX(CASE UCP.PermissionName WHEN 'Recover User State' THEN 'X' END) as [Recover User State],
   MAX(CASE UCP.PermissionName WHEN 'Remote Control' THEN 'X' END) as [Remote Control],
   MAX(CASE UCP.PermissionName WHEN 'View AI' THEN 'X' END) as [View AI],
   MAX(CASE UCP.PermissionName WHEN 'View BMC' THEN 'X' END) as [View BMC],
   MAX(CASE UCP.PermissionName WHEN 'View Collected File' THEN 'X' END) as [View Collected File]
FROM
   dbo.v_SecuredObject as SO
   INNER JOIN dbo.v_UserClassPermNames as UCP
      ON SO.ObjectKey = UCP.ObjectKey
GROUP BY
   UCP.UserName,
   SO.ObjectName
ORDER BY
   UCP.UserName,
   SO.ObjectName