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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: