SharePoint Utility Query: Portal Structure Report with Security Permissions

I have a number of queries that I use to manage SharePoint an I wanted to share a few of the more useful ones. On one large portal, there is a proliferation of Sub-Areas which are managed by a number of different users in several different companies. Some of these areas use unique permissions extensively and it has become difficult to keep track of who has what permissions. I use this query to list out the portal structure and show which users and groups have what permissions on the area. This query also makes use of the function I wrote about in a previous article which decodes the portal permissions.

DECLARE @SiteRoot VARCHAR(255)
DECLARE @AreaNav VARCHAR(4000)

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

SET @SiteRoot = ‘http://localhost/’
SET @AreaNav = ‘Topics’ SELECT
@SiteRoot + Webs.FullUrl + ” AS [URL]
, @SiteRoot + Webs.FullUrl + ‘/_layouts/1033/spcatsec.aspx?CatID=’
+ convert(varchar(255), CatPath.CatID) as [Manage]
, case CatDef.InheritSecurity
WHEN 1 then ‘Inherits’
WHEN 0 then ‘Unique’
END AS [PermSetting]
, CatPath.Path AS [AreaNav]
, CASE WHEN MemberIsUser=0 and WebGroups.Title IS NULL THEN
(SELECT top 1 title
FROM webgroups
WHERE siteid=Webs.siteid and id=portalperm.UserGroupID)
ELSE ISNULL(WebGroups.Title ,UserInfo.tp_Login)
END AS [GroupOrUser]
, ISNULL(WebGroups.[description],SharePointHelper.dbo.sFn_DecodePermMask(PortalPerm.PortalMask)) AS [Rights]
FROM
webs
LEFT OUTER JOIN CatDef
ON replace( CatDef.weburl,’*’,”) = Webs.fullurl
LEFT OUTER JOIN CatPath
ON CatDef.catid=CatPath.catid
LEFT OUTER JOIN portalperm
ON portalperm.permid=CatPath.permid
LEFT OUTER JOIN WebGroups
ON (portalperm.UserGroupID = WebGroups.[id] and WebGroups.webid=Webs.[id])
LEFT OUTER JOIN userinfo
ON (portalperm.UserGroupID= UserInfo.tp_ID) and (UserInfo.tp_SiteID=Webs.siteid)
WHERE (CatPath.Path IS NOT NULL) and ( CatPath.Path like ‘%’ + @AreaNav + ‘%’)
ORDER BY CatPath.Path, [GroupOrUser], userinfo.tp_login

The abridged output looks similar to this: