SQL Queries for SharePoint Content Database (SP groups related)
Recently I need to build some SQL reports with SSRS and come across this topic. By search on web I can find several sources provide the SQL statements. However most of them are not working on SP2016. Hence I re-write the SQL statement and share here. I tested in SP2016 on-premise only.
Query to get all the members of the SharePoint Groups
select dbo.GroupMembership.GroupId, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM
dbo.UserInfo INNER join dbo.GroupMembership ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID
INNER join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
order by groupid
Query to get all SharePoint Groups without member
select dbo.Groups.ID, dbo.Groups.Title, dbo.GroupMembership.MemberId FROM
dbo.GroupMembership right outer join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId is null
Please note that if you created a SharePoint group with system account (SHAREPOINT\system) the account will be added as the member of new group. However system account is hidden in the web interface. In this case the group is actually NOT empty. You may need to re-write my SQL statement to handle this special case.
- update here, here is SQL statement to handle SHAREPOINT\system with UNION:
(select dbo.Groups.ID, dbo.Groups.Title FROM
dbo.GroupMembership right outer join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId is null)
UNION
(select dbo.Groups.ID, dbo.Groups.Title FROM
dbo.GroupMembership join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.Groups.ID in (select dbo.Groups.ID FROM dbo.GroupMembership join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId in (select tp_ID from dbo.UserInfo where tp_Login = ‘SHAREPOINT\system’))
group by dbo.Groups.ID,dbo.Groups.Title having count(dbo.GroupMembership.MemberId)=1)
order by title
Originally published at http://createsitecollection.wordpress.com on August 21, 2019.
