Query SharePoint database for user permissions

This query is used to find what permissions a user has been granted to sites, lists, and items. This will only show if a user has been explicitly given the permissions.


SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login, dbo.Perms.ScopeUrl
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID INNER JOIN
dbo.Perms ON dbo.RoleAssignment.ScopeId = dbo.Perms.ScopeId


This query is used to find what permissions a group has been granted to sites, lists, and items.


SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.Groups.Title AS GroupName, dbo.Perms.ScopeUrl
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID INNER JOIN
dbo.Perms on dbo.RoleAssignment.ScopeId = dbo.Perms.ScopeId


This query is used to find what users are members of which groups


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 and dbo.GroupMembership.GroupId = dbo.Groups.ID INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

Origin https://lelandholmquest.wordpress.com/2011/06/06/query-sharepoint-2007-database-for-user-permissions/