Query SharePoint 2007 database for user permissions

This is another tidbit generated by Richard Granger. Permissions are a critical component to managing a SharePoint based solution. Dealing with permissions can be very simple, but they can also quickly become a nightmare or worse to manage, The nice thing about SharePoint is that it enables you to lock down content down to the individual record/item level. The bad thing about SharePoint is that it enables you to lock down … Dealing with permissions outside of the group level can be necessary, but make sure you are signing up for the additional management and understand the consequences.

One thing that can make it easier is to be able to pull from the database all the user’s permissions; whether by group or through explicit permissions. Here are the queries that Richard shared with us:

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. For instance if John Doe is a member of the Test Owners group and the Owners group has full control of the site Test Site then this query will not return John Doe. But if John Doe is given full control to the Test Site he will be returned in the query.

   1: SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, 

   2: dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login, dbo.Perms.ScopeUrl 

   3: FROM dbo.RoleAssignment INNER JOIN 

   4: dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND 

   5: dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN 

   6: dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN 

   7: dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID INNER JOIN 

   8: 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.

   1: SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, 

   2: dbo.Groups.Title AS GroupName, dbo.Perms.ScopeUrl 

   3: FROM dbo.RoleAssignment INNER JOIN 

   4: dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND 

   5: dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN 

   6: dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN 

   7: dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND 

   8: dbo.RoleAssignment.PrincipalId = dbo.Groups.ID INNER JOIN 

   9: dbo.Perms on dbo.RoleAssignment.ScopeId = dbo.Perms.ScopeId 

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

   1: SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login 

   2: FROM dbo.GroupMembership INNER JOIN 

   3: dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId and dbo.GroupMembership.GroupId = dbo.Groups.ID INNER JOIN 

   4: dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

Thanks for the queries Richard!

Later, I’ll show how to take these queries and create useful webparts. Ninja

Advertisements

About lelandholmquest

After serving in the Navy as a Reactor Operator on fast attack submarines, I earned both a Bachelor's and Masters in Information Technology from American InterContinental University and am currently working on my doctorate. I have a beautiful wife and two of the sweetest daughters a man could ask for. And I work for the greatest company: Microsoft. At Microsoft I work on the knowledge management system for Microsoft Services: Campus on Office 365.
This entry was posted in SharePoint Content Management Tips, SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s