Security Report - Dashboard for Admin- Access

WITH ProfileEntity AS (
    SELECT
        h.CubeName,
        h.ProfileKey,
        h.ProfileName,
        e.EntityMemberID
    FROM WorkflowProfileHierarchy h
    JOIN WorkflowProfileEntities e
        ON h.ProfileKey = e.WorkflowProfileKey
    WHERE h.ProfileType = 3
      AND e.EntityMemberID = (
            SELECT MemberID
            FROM Member
            WHERE Name = '|!SelectedEntity!|' ' This is Parameter to call entity.
              AND DimTypeID = 0
        )

    UNION

    SELECT
        h.CubeName,
        h.ProfileKey,
        h.ProfileName,
        e.EntityMemberID
    FROM WorkflowProfileHierarchy h
    JOIN WorkflowProfileEntities e
        ON h.ParentProfileKey = e.WorkflowProfileKey
    WHERE h.ProfileType = 3
      AND e.EntityMemberID = (
            SELECT MemberID
            FROM Member
            WHERE Name = '1100'
              AND DimTypeID = 0
        )
),

AttributeData AS (
    SELECT
        pe.CubeName,
        pe.ProfileName,
        m.Name AS EntityName,
        WPA.AttributeIndex,
        CASE TRY_CONVERT(uniqueidentifier, WPA.ProfileAttributeValue)
            WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
            WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
            ELSE ISNULL(sg.Name,'Unknown')
        END AS SecurityGroup
    FROM ProfileEntity pe
    JOIN WorkflowProfileAttributes WPA
        ON pe.ProfileKey = WPA.ProfileKey
    JOIN Member m
        ON pe.EntityMemberID = m.MemberID
       AND m.DimTypeID = 0
    LEFT JOIN SecGroup sg
        ON TRY_CONVERT(uniqueidentifier, WPA.ProfileAttributeValue) = sg.UniqueID
    WHERE WPA.AttributeIndex IN (1250,16100,6100,6200,6300)
      AND pe.ProfileName NOT LIKE '%.%'
),

PivotAttributes AS (
    SELECT
        CubeName,
        ProfileName,
        EntityName,
        [1250]  AS WorkflowExecutionGroup,
        [16100] AS CertificationSignOffGroup,
        [6100]  AS JournalProcessGroup,
        [6200]  AS JournalApprovalGroup,
        [6300]  AS JournalPostGroup
    FROM AttributeData
    PIVOT (
        MAX(SecurityGroup)
        FOR AttributeIndex IN ([1250],[16100],[6100],[6200],[6300])
    ) p
)

SELECT
    ROW_NUMBER() OVER (ORDER BY p.EntityName, p.ProfileName) AS RowID,
    p.CubeName,
    p.EntityName,
    p.ProfileName AS WorkflowProfileName,

    CASE m.ReadDataGroupUniqueID
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgR1.Name,'Unknown')
    END AS ReadGroup,

    CASE m.ReadDataGroupUniqueID2
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgR2.Name,'Unknown')
    END AS ReadGroup2,

    CASE m.ReadWriteDataGroupUniqueID
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgW1.Name,'Unknown')
    END AS WriteGroup,

    CASE m.ReadWriteDataGroupUniqueID2
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgW2.Name,'Unknown')
    END AS WriteGroup2,

    p.WorkflowExecutionGroup,
    p.CertificationSignOffGroup,
    p.JournalProcessGroup,
    p.JournalApprovalGroup,
    p.JournalPostGroup

FROM PivotAttributes p
JOIN ProfileEntity pe
    ON p.ProfileName = pe.ProfileName
JOIN Member m
    ON pe.EntityMemberID = m.MemberID
   AND m.DimTypeID = 0
LEFT JOIN SecGroup sgR1 ON m.ReadDataGroupUniqueID  = sgR1.UniqueID
LEFT JOIN SecGroup sgR2 ON m.ReadDataGroupUniqueID2 = sgR2.UniqueID
LEFT JOIN SecGroup sgW1 ON m.ReadWriteDataGroupUniqueID  = sgW1.UniqueID
LEFT JOIN SecGroup sgW2 ON m.ReadWriteDataGroupUniqueID2 = sgW2.UniqueID
ORDER BY
    p.EntityName,
    p.ProfileName;

Hi MarcusH,

Thanks for the code, with the above code i can be able to build complete query to get access details based on entity. this code will provide entity access group details and workflow access group details