ProjectServer

Now we need to create SQL query which return Global Permissions for all users

SELECT TOP (100) PERCENT r.RES_UID, CASE WHEN (MAX(CAST(cp.WSEC_ALLOW AS int)) = 1 AND MAX(CAST(cp.WSEC_DENY AS int)) = 0)

THEN 1 ELSE 0 END AS access, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID

FROM dbo.MSP_RESOURCES AS r WITH (NOLOCK) INNER JOIN

dbo.MSP_WEB_SECURITY_GROUP_MEMBERS AS GM WITH (NOLOCK) ON GM.WRES_GUID = r.RES_SECURITY_GUID INNER JOIN

dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS cr WITH (NOLOCK) ON cr.WSEC_SP_GUID = GM.WSEC_GRP_GUID AND

cr.WSEC_CAT_UID IS NULL INNER JOIN

dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS cp WITH (NOLOCK) ON cp.WSEC_REL_UID = cr.WSEC_REL_UID INNER JOIN

dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS WITH (NOLOCK) ON

cp.WSEC_FEA_ACT_UID = dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID

GROUP BY r.RES_UID, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID,

dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID

As you can see I’m using (MAX(CAST(cp.WSEC_ALLOW AS int)) = 1 AND MAX(CAST(cp.WSEC_DENY AS int)) = 0) to aggregate permission thouht different security categories, is you need to display  details remove this aggregation

There is a result J

But we will need transform permission from rows to columns. Because XML  will be generated from this Query  I need something better than GUID of permissions for element names. So as column name I’ll use ID of the string with permission name with ‘n’ on beginning.

select * from

(

select RES_UID as RUID, [90102] as n90102,[90103] as n90103,[90104] as n90104,[90105] as n90105,[90203] as n90203,[90210] as n90210,[90403] as n90403,[90501] as n90501,[90503] as n90503,[90504] as n90504,[90505] as n90505,[90507] as n90507,[90602] as n90602,[90604] as n90604,[90710] as n90710,[90720] as n90720,[90730] as n90730,[90740] as n90740,[90750] as n90750,[90760] as n90760,[90770] as n90770,[90780] as n90780,[90790] as n90790,[91004] as n91004,[91005] as n91005,[91006] as n91006,[91007] as n91007,[91010] as n91010,[91011] as n91011,[91016] as n91016,[91021] as n91021,[92005] as n92005,[92007] as n92007,[92008] as n92008,[92010] as n92010,[92011] as n92011,[92012] as n92012,[92018] as n92018,[92021] as n92021,[92030] as n92030,[92031] as n92031,[92032] as n92032,[92033] as n92033,[92035] as n92035,[92042] as n92042,[92043] as n92043,[92044] as n92044,[92045] as n92045,[92046] as n92046,[92047] as n92047,[92048] as n92048,[92049] as n92049,[92052] as n92052,[92053] as n92053,[92055] as n92055,[92061] as n92061,[92064] as n92064,[92065] as n92065,[92066] as n92066

FROM

(

SELECT     TOP (100) PERCENT r.RES_UID, CASE WHEN (MAX(CAST(cp.WSEC_ALLOW AS int)) = 1 AND MAX(CAST(cp.WSEC_DENY AS int)) = 0)

                      THEN 1 ELSE 0 END AS access, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID

FROM         dbo.MSP_RESOURCES AS r WITH (NOLOCK) INNER JOIN

                      dbo.MSP_WEB_SECURITY_GROUP_MEMBERS AS GM WITH (NOLOCK) ON GM.WRES_GUID = r.RES_SECURITY_GUID INNER JOIN

                      dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS cr WITH (NOLOCK) ON cr.WSEC_SP_GUID = GM.WSEC_GRP_GUID AND

                      cr.WSEC_CAT_UID IS NULL INNER JOIN

                      dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS cp WITH (NOLOCK) ON cp.WSEC_REL_UID = cr.WSEC_REL_UID INNER JOIN

                      dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS WITH (NOLOCK) ON

                      cp.WSEC_FEA_ACT_UID = dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID

GROUP BY r.RES_UID, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID

) p

  PIVOT (    COUNT(access)     FOR WSEC_FEA_ACT_NAME_ID IN   

( [90102],[90103],[90104],[90105],[90203],[90210],[90403],[90501],[90503],[90504],[90505],[90507],[90602],[90604],[90710],[90720],[90730],[90740],[90750],[90760],[90770],[90780],[90790],[91004],[91005],[91006],[91007],[91010],[91011],[91016],[91021],[92005],[92007],[92008],[92010],[92011],[92012],[92018],[92021],[92030],[92031],[92032],[92033],[92035],[92042],[92043],[92044],[92045],[92046],[92047],[92048],[92049],[92052],[92053],[92055],[92061],[92064],[92065],[92066] )) as p

) piv INNER JOIN dbo.MSP_RESOURCES AS r  WITH (NOLOCK) on piv.RUID=r.RES_UID  WHERE r.RES_TYPE in (1,2,101,102) order by RES_NAME

So now we have 2 queries which returning required data, and we can start with web part.