Friday, June 15, 2012

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)
                                                    ON
                                          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
GO

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)

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)
                                                    ON
                                          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
GO


2. Excel
Excel has a connection to the view.

Data on the tab “Report_data” got refreshed every time the worksbook is open in Excel app
ATTENTION: IF YOU HAVE PUT A WORKBOOK IN THE SHAREPOINT 2010 IT WILL NOT GET REFRESHED IN THE BROWSER via xlviewer.aspx
You need to open it in the excel application to refresh the data.


The tab “Report” hosts a pivot table based on the tab “Report_Data”



Good luck with auditing!