This SQL query pulls all SMS permissions for each user and displays them in a matrix. Put this into an SMS 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 '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 'Manage Folder' THEN 'X' END) as [Manage Folder],
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 Resource' THEN 'X' END) as [Modify Resource],
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 'Remote Control' THEN 'X' END) as [Remote Control],
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
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
