In some cases you may want to audit who has what permissions to various parts of your SharePoint site or farm.<\/p>\n
This query will give you a list of all of your groups, the roles \/ permissions of the groups, the users in those groups, and what they have permissions to:<\/p>\n
select ra.PrincipalId as 'GroupID'\n, g.Title 'GroupTitle'\n, p.ScopeUrl\n, r.Title 'RoleTitle'\n, ui.tp_Title 'UserOrADgroup' \/* Comment out to exclude individual users *\/\nfrom RoleAssignment ra with (nolock)\n\u00a0\u00a0\u00a0\u00a0 left outer join Roles r\u00a0 with (nolock) on ra.RoleId = r.RoleId\n\u00a0\u00a0\u00a0 \u00a0left outer join Perms p\u00a0 with (nolock) on ra.ScopeId = p.ScopeId\n\u00a0\u00a0\u00a0 \u00a0left outer join GroupMembership gm\u00a0 with (nolock) on ra.PrincipalId = gm.GroupId \/* Comment out to exclude individual users *\/\n left outer join Groups g\u00a0 with (nolock) on gm.GroupId = g.ID\n--\u00a0 \u00a0left outer join Groups g\u00a0 with (nolock) on ra.PrincipalId = g.ID \/* Use if only querying for groups *\/\n\u00a0\u00a0\u00a0 \u00a0left outer join UserInfo ui\u00a0 with (nolock) on gm.MemberId = ui.tp_ID \/* Comment out to exclude individual users *\/\nwhere \u00a0p.ScopeUrl like '%' AND tp_Title not like 'NULL' AND tp_Title not like 'System Account' AND p.ScopeUrl not like '_c%'\ngroup by g.Title, P.ScopeUrl, ra.PrincipalId, r.Title order by p.ScopeUrl, g.Title<\/pre>\nTo get all Groups and their membership:<\/p>\n
SELECT dbo.Groups.ID\n, dbo.Groups.Title\n, dbo.UserInfo.tp_Title\n, dbo.UserInfo.tp_Login \nFROM dbo.GroupMembership \nINNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId \nINNER JOIN dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID \nOrder by Title<\/pre>\n