Introduction

You may be asking yourself, what exactly are SharePoint role assignments?!  This article will discuss the following role assignment topics:

• Definition
• Growth
• SharePoint Impact
• Discovery
• Maintenance

Definition

A role assignment is the relationship between role definitions, users and groups, and scopes.  Let's break that down a little further!

Role Definition

Role definition is synonymous with permission level.  So, the default permission levels such as Full Control, Contribute, Read are individual role definitions.

Users and Groups

SharePoint best practice recommends that you grant permissions using groups.  These groups can be SharePoint groups or Active Directory Groups.  You can also grant permissions directly to a user account.

Scope

Scope determines WHERE permission applies.  For example, to a web site, to a list or library, or an individual list or library item.

Role Definition Examples

So now we know what a role definition is, let's see some examples!

Growth

As your portal grows, so will permissions and in turn, role assignments.  Also, if you do a lot of work with item level security, this will have a significant impact on the number of role assignments.  The depth of your portal and granularity of permissions will also impact the number of role assignments.

Growth Example

Here's a quick example of how role assignments can grow exponentially.

• Portal site http://portal
• Sub site http://portal/hr
• Sub site http://portal/hr/team
• A list named "Restricted" with 1,000 items
• Item level permissions on every item in the list
• For each item, one user is granted contribute permission

How many role assignments do you think you have?  For this list alone, there would be 4,000 role assignments.  Say what?!  Here's how it breaks down:

• 1,000 list items with item level permission per item = 1,000 role assignments
• http://portal/hr/team will receive 1,000 "Limited Access" role assignments
• http://portal/hr will receive 1,000 "Limited Access" role assignments
• http://portal will receive 1,000 "Limited Access" role assignments

Permissions must be carefully planned and implemented.  Otherwise role assignments can grow out of control.  I recently finished cleaning up a site collection that had over 5 MILLION role assignments.  Yikes!

SharePoint Impact

A large number of role assignments will lead to performance degradation.  When a user accesses an item, such as a site, page, list, library or item, role assignments have to be checked to determine permission.  If the role assignments list is huge, this will impact the amount of time it takes to figure out the user's permissions.  Other operations such as viewing list/library permissions, granting/revoking permissions and removing users from site collections will also suffer.

For example, on our troubled site collection with over 5 million role assignments, it could take up to 2 hours to remove a user from the site collection.  Once role assignments were cleaned up, removal time decreased to about 10 seconds.

Discovery

The following T-SQL query can be ran against individual content databases to display all the role assignments.

SELECT TOP 250000
dbo.Perms.ScopeUrl, dbo.Roles.Title AS RoleTitle, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
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

Maintenance

You may find, like I did, that your permissions are out of control and need to be cleaned up.  This can be done in a variety of ways, including but not limited to:

• Resetting list inheritance
• Resetting list item inheritance
• Removing list permissions

You can find information and example PowerShell scripts for all of these activities here.

Conclusion

I hope you enjoyed the article.  Now go forth and conquer!