SQL queries for Sharepoint 2010 Site and User Permissions

In some cases you may want to audit who has what permissions to various parts of your SharePoint site or farm.

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:

select ra.PrincipalId as 'GroupID'
, g.Title 'GroupTitle'
, p.ScopeUrl
, r.Title 'RoleTitle'
, ui.tp_Title 'UserOrADgroup' /* Comment out to exclude individual users */
from RoleAssignment ra with (nolock)
     left outer join Roles r  with (nolock) on ra.RoleId = r.RoleId
     left outer join Perms p  with (nolock) on ra.ScopeId = p.ScopeId
     left outer join GroupMembership gm  with (nolock) on ra.PrincipalId = gm.GroupId /* Comment out to exclude individual users */
   left outer join Groups g  with (nolock) on gm.GroupId = g.ID
--   left outer join Groups g  with (nolock) on ra.PrincipalId = g.ID /* Use if only querying for groups */
     left outer join UserInfo ui  with (nolock) on gm.MemberId = ui.tp_ID /* Comment out to exclude individual users */
where  p.ScopeUrl like '%' AND tp_Title not like 'NULL' AND tp_Title not like 'System Account' AND p.ScopeUrl not like '_c%'
group by g.Title, P.ScopeUrl, ra.PrincipalId, r.Title order by p.ScopeUrl, g.Title

To get all Groups and their membership:

SELECT dbo.Groups.ID
, dbo.Groups.Title
, dbo.UserInfo.tp_Title
, dbo.UserInfo.tp_Login 
FROM dbo.GroupMembership 
INNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId 
INNER JOIN dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID 
Order by Title

Other user Sharepoint Content Database Links: http://www.demantprasad.com/search/label/Useful%20SQL%20Queries%20for%20SharePoint%20Content%20Database