Sharing the experience: SharePoint: End users access audit. How to pull users and their groups for the site

Your SharePoint Audit requires to clean the users on the sites. How do you proceed with this?

In case you don't a have third-party tool you have to be creative. You want to have a report that shows what user in what group in what subsite.

Here what I did.

I used an excel automated report since we need to change the membership and we want to reflect this changes fast in the report. Once I have created the report I put the excel on SharePoint , this way people can see the current situation with user membership.

Be mindful, in case you have assigned users directly on site , the report below doesn't show them.

Assumptions are:

- All users have been given access via group membership ;

- The spweb (subsite) uses unique permission levels (site collection inheritance is broken).

- The report shows the data for specific site collection

In case you need to enhance present limitations, you need to change a SQL query.

That what I did:

1. SQL

I have created a view:

ALTER view [dbo].[UserGroupAssignment] as

select UserInfo.tp_Title AS [User],

UserInfo.tp_Login AS [User ID],

UserInfo.tp_Email AS Email,

Groups.Title as [Group],

WebGroups.WebName as [Site]

from WSS_Content_EP..UserInfo (nolock)

join WSS_Content_EP..GroupMembership (nolock) on UserInfo.tp_SiteID=GroupMembership.SiteId and UserInfo.tp_ID=GroupMembership.MemberId

join WSS_Content_EP..Groups (nolock) on Groups.SiteId=UserInfo.tp_SiteID and groups.ID=GroupMembership.GroupId

join

(

SELECT Min(webs.title) AS WebName,

Min(groups.title)AS GroupName ,

Groups.ID

FROM WSS_Content_EP..[roles] (nolock)

JOIN WSS_Content_EP..roleassignment (nolock)

ON roleassignment.siteid = roles.siteid

AND roleassignment.roleid = roles.roleid

AND roleassignment.scopeid IN (SELECT [perms].[scopeid]

FROM WSS_Content_EP..[perms] (nolock)

JOIN WSS_Content_EP..webs (nolock)

perms.scopeurl = webs.fullurl

AND webs.siteid = perms.siteid

AND webs.id = perms.webid

WHERE

[perms].siteid = '55FE9630-5420-48F3-9099-210AEEEF43A8'

AND webs.fullurl NOT LIKE 'apps')

INNER JOIN WSS_Content_EP..webs (nolock)

ON roles.siteid = webs.siteid

AND roles.webid = webs.id

AND webs.scopeid = roleassignment.scopeid

INNER JOIN WSS_Content_EP..groups (nolock)

ON roleassignment.siteid = groups.siteid

AND roleassignment.principalid = groups.id

WHERE roles.siteid = '55FE9630-5420-48F3-9099-210AEEEF43A8'

GROUP BY groups.id

) as WebGroups on WebGRoups.ID=Groups.ID

where tp_SiteID='55FE9630-5420-48F3-9099-210AEEEF43A8'

and UserInfo.tp_IsActive=1

--order by WebName,Title

P.S.

siteid is your site collection id where you want get the report from.

WSS_Content_EP - your content db where you site collection resides.

web.fullurl not like 'apps' - I am not including a root site for site collections apps.

Created an report account who has a read-only access for the view and underlying the tables

The query doesn’t include the spwebs that have permission level inherited from site collection level. The subquery WebGroups currently cuts them, probably because we don’t have these webs in the table “roles” (webid)