Listing all DotNetNuke Pages with Settings and Permissions

To document the pages of a DNN installation, we can access the relevant tables in a DNN database:

Tabs	Pages
Roles	DNN Roles
Permission	Permitted action
TabPermission	Assignment of role privileges per page

The SQL statement consists of sub-selects to

• concatenate the permissions for a role and a page
• concatenate the role permissions in a page

SELECT TabID, TabName, Title, [PageHeadText], TabPath, 
  IsVisible AS InMenu,
  -- IsSecure, Url, (etc. as needed) 
  SkinSrc, ContainerSrc,
  SUBSTRING((  
    SELECT '; ' 
      + ISNULL(r.RoleName, CONVERT(NVARCHAR, tp.RoleID)) + ' [' 
      + SUBSTRING((
          SELECT ', ' + PermissionName 
          FROM TabPermission tpSub 
          INNER JOIN Permission p 
            ON p.PermissionID = tpSub.PermissionID
          WHERE tpSub.TabID = tp.TabID 
            AND tpSub.RoleID = tp.RoleID
          FOR XML PATH('')), 
        3, 1000)
      + ']'
    FROM TabPermission tp
    LEFT OUTER JOIN 
      (SELECT RoleID, RoleName FROM Roles
      UNION ALL SELECT -1, 'All Users'
      UNION ALL SELECT -2, 'Registered'
      UNION ALL SELECT -3, 'Unauthorized') r 
        ON tp.RoleID = r.RoleID

    WHERE tp.TabPermissionID IN
      (SELECT MIN(TabPermissionID) 
      FROM  TabPermission tpMin 
      WHERE tpMin.TabID = Tabs.TabID
      GROUP BY tpMin.RoleID
      )
    ORDER BY tp.RoleID
    FOR XML PATH('')), 3, 1000) AS Permissions
FROM Tabs
WHERE PortalID = 0
AND IsDeleted = 0
--AND Level = 0
ORDER BY TabPath

The negative values for RoleID are from an answer on SO.

The query result looks like this (new installation, Admin pages excluded):

TabName	TabPath	M.	SkinSrc	ContainerSrc	Permissions
About Us	//AboutUs	0	[G]Skins/ DarkKnight/ 2-Column-Right-Mega-Menu.ascx	[G]Containers/ DarkKnight/ SubTitle_Grey.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
Collaborate Details	//CollaborateDetails	0	[L]Skins/ Collaborate/ Details.ascx	[L]Containers/ Collaborate/ Simple.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
Collaborate Home	//CollaborateHome	0	[L]Skins/ Collaborate/ Home.ascx	[L]Containers/ Collaborate/ Simple.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
Getting Started	//GettingStarted	1	[G]Skins/ DarkKnight/ Home-Mega-Menu.ascx	[G]Containers/ DarkKnight/ SubTitle_Grey.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
Home	//Home	0	[G]Skins/ DarkKnight/ Home-Mega-Menu.ascx	[G]Containers/ DarkKnight/ SubTitle_Grey.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
News & Promotions	//NewsPromotions	0	[G]Skins/ DarkKnight/ 2-Column-Right-Mega-Menu.ascx	[G]Containers/ DarkKnight/ SubTitle_Grey.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]
Our Services	//OurServices	0	[G]Skins/ DarkKnight/ 2-Column-Right-Mega-Menu.ascx	[G]Containers/ DarkKnight/ SubTitle_Grey.ascx	All Users [View Tab]; Administrators [View Tab, Edit Tab]

(Some column are omitted to fit page width)

Add or remove columns and conditions as required for your purpose.