Need list of all SharePoint Farm users with associated objects and permissions

Question

Greetings,

I'm looking for a little SQL to QUERY (with NOLOCK, of course) from the raw SharePoint SQL Content DBs to produce a list of users, objects and their assigned permissions.

I have done other "raw" queries without problems.... for a list of all users enrolled within a site ... but I'm stuck on how to determine WHAT OBJECTS (Web, Lists, Items) the users have access to and what access level they have been granted.

Can you please point me in the right direction?

Thanks!

//W

Answer 1

Danger Will! Assuming you're an adult and are aware of the risks ;)

Inspecting the SharePoint content database gives a schema and some useful SQL snippets such as :-

-- Query to get all the members of the SharePoint 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 INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID