Security on a Portal

So the question came up for how to view the entire security model for the Pages and Module Instances of a Portal, and I realized that while we have a handful of security reports for other things, we don't have a generic query pulling security on those items. So I spent an hour and a half (probably shouldn't have taken me that long, but I'm a bit out of practice) and what I came up with shows the page structure (in order, just like in Pages) with the permissions for the page, and within each page it shows the modules on that page (in their template frames and in order within those frames) and the permissions on those module instances. It shows permissions as "Roles | Persons" in case you have person-level security on anything, and each of those is detailed as "Name (permissions)" where the permissions are outlined as “V”=View, “E”=Edit, ”S”=Edit Security, ”P”=Edit People, “M”=Edit Modules, “N”=Edit Notes, “A”=Approve, and “R”=Edit Registrations.

DECLARE @portalID INT,@a INT
SET @portalID=1

-- Pages
DECLARE @pagetable TABLE (page_id INT, page_name VARCHAR(500),permission_list VARCHAR(MAX),ordering INT)

;WITH pagetree (
Root
, Parent
, Name
, Level
, ID
, Branch
) AS (
SELECT
ppp.page_name
, CONVERT(VARCHAR(100),'')
, ppp.page_name
, 0
, ppp.page_id
, CONVERT(VARCHAR(MAX),REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
WHERE
ppp.page_id=(SELECT TOP 1 default_page_id FROM port_portal WHERE portal_id=@portalID)
UNION ALL
SELECT
tree.Root
, tree.Name
, CONVERT(VARCHAR(100),(replicate('-',tree.level+1))+ppp.page_name)
, tree.Level + 1
, ppp.page_id
, CONVERT(VARCHAR(MAX),tree.Branch + '.' + REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
JOIN pagetree AS tree ON ppp.parent_page_id = tree.ID
)

INSERT INTO @pagetable
SELECT
pt.ID AS page_id
,pt.Name
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
,ROW_NUMBER() OVER (ORDER BY @a )
FROM
pagetree AS pt
ORDER BY
Root
, branch


-- Modules
DECLARE @moduletable TABLE (page_id INT, module_instance_id INT,module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),template_frame_name VARCHAR(500),module_order INT, permission_list VARCHAR(MAX))

INSERT INTO @moduletable
SELECT
pmi.page_id
,pmi.module_instance_id
,pmi.module_title
,pm.module_name
,pm.module_url
,pmi.template_frame_name
,pmi.template_frame_order
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
FROM port_module_instance AS pmi WITH(NOLOCK)
JOIN port_module AS pm WITH(NOLOCK) ON pm.module_id=pmi.module_id
WHERE page_id IN (SELECT page_id FROM @pagetable)

DECLARE @finaltable TABLE (ordering varchar(500), page_id INT,page_name VARCHAR(500),template_frame_name VARCHAR(500),module_instance_id INT, module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),permission_list VARCHAR(MAX))
INSERT INTO @finaltable
SELECT REPLACE(STR(CONVERT(VARCHAR,pt.ordering), 9, 0), ' ', '0'),pt.page_id,pt.page_name,NULL AS template_frame_name,NULL AS module_instance_id, NULL AS module_title, NULL AS module_type, NULL AS module_url,pt.permission_list FROM @pagetable pt
UNION
SELECT (SELECT REPLACE(STR(CONVERT(VARCHAR,ordering), 9, 0), ' ', '0') FROM @pagetable WHERE page_id=mt.page_id)+CONVERT(VARCHAR,mt.module_order),mt.page_id,'zzzzzzzzzzzzz' AS page_name,mt.template_frame_name,mt.module_instance_id,mt.module_title,mt.module_type,mt.module_url,mt.permission_list FROM @moduletable mt

SELECT
page_id
,REPLACE(page_name,'zzzzzzzzzzzzz','') AS page_name
,template_frame_name
,module_instance_id
,module_title
,module_type
,module_url
,permission_list
FROM @finaltable
ORDER BY ordering