SharePoint bug in SPWeb.AllRolesForCurrentUser

29 June, 2009

SharePoint bug in SPWeb.AllRolesForCurrentUser

I’ve finally tracked down the bug in SPWeb.AllRolesForCurrentUser. This has been mysteriously failing for some users in some set ups when using the SharePoint Learning Kit as discussed on the CodePlex forums.

When this happened to a customer, I was able to debug it and found out the problem. I can now re-produce it at will and it’s definitely a bug in SharePoint.

The problem occurs when the user is a member of a large number of SharePoint groups. To retrieve all the roles, SharePoint makes a call to the database. In normal circumstances this call is along the lines of 

SELECT DISTINCT ra.RoleId FROM RoleAssignment AS ra WHERE ra.ScopeId = ’67FD4879-2097-4B98-9771-5A6E9D11F0E9′ AND ra.PrincipalId IN (3,1 )

where 3 and 1 are the IDs of the groups and the guid (67FD4879-2097-4B98-9771-5A6E9D11F0E9) is the ID of the site. This call is retrieving all the role assignments on this site for all the groups that the user is a member of.

Now when the user is a member of a large number of groups the SQL generated is:

SELECT DISTINCT ra.RoleId FROM RoleAssignment AS ra WHERE ra.ScopeId = ’67FD4879-2097-4B98-9771-5A6E9D11F0E9′ AND ra.PrincipalId IN (SELECT DISTINCT ra.RoleId FROM RoleAssignment AS ra WHERE ra.ScopeId = ’67FD4879-2097-4B98-9771-5A6E9D11F0E9′ AND ra.PrincipalId IN (839,1840,1841,…….,2611,2612,1

The …….. is where I’ve removed about 740 group IDs.

I’m not sure what’s happening here in the SQL generation, but the “SELECT .. FROM .. WHERE .. IN(” bit is repeated twice leading to invalid SQL. Apart from being a nonsense statement there are 2 opening braces and only 1 closing brace. The code is obviously getting confused by so many groups. My initial thought was that it allocated a buffer for the string generation, but it wasn’t big enough, but that doesn’t explain the 2 sets of SELECT … .

This is easy to reproduce. All you need to do is create a large number of groups with the current user in, and then call AllRolesForCurrentUser, not forgetting to delete them all afterwards or you’ll quickly clutter up your server. I found 800 is enough to make it fail for me, different environment may need more. A sample test class is shown at then end.

In normal use, no user is going to be a member of this many groups – for a start membership of this many is going to need to be automated. However, the best use of SLK requires a site per class and in a reasonably sized Secondary school, this can easily be over 1000 members. Then it’s generally only the administrator who is affected if his account has been used to create the sites, and only if you have a group per site for membership purposes.

I don’t normally create groups for class sites anyway. The maintenance screens for groups are just not very user friendly – you have to manually page through the lists to find a group – and it makes the year roll-over a pain. My preference is to give the users explicit permissions on the class sites rather than through groups, something I’ve discussed before.


using System; using System.Collections.Generic; using System.Globalization; using System.IO; using Microsoft.SharePoint; namespace SharePointTest {  public class AllRolesTest  {    public static void Main()    {      //Url to a SharePoint site. Any site but don’t do it on a live server.      string url = “http://salamanderdemo/sites/mis”;      //The username of the user you are running as. I could have programmatically found this out, but it      //wouldn’t have added anything to the example      string userName = “DEMO\Administrator”;      try      {        using (SPSite site = new SPSite(url))        {          using (SPWeb web = site.OpenWeb())          {            SPUser user = web.SiteUsers[userName];            // This call should succeed            SPRoleDefinitionBindingCollection allRoles = web.AllRolesForCurrentUser;            Console.WriteLine(“First AllRolesForCurrentUser worked.”);            List names = new List();            try            {              //Create all the groups              for (int i = 0; i < 800; i++)              {                if (i%100 == 0)                {                  Console.WriteLine("Added {0} groups.", i);                }                string name = Guid.NewGuid().ToString();                names.Add(name);                web.SiteGroups.Add(name, user, user, name);              }              try              {                // This call should fail                allRoles = web.AllRolesForCurrentUser;                Console.WriteLine("AllRolesForCurrentUser succeeded, try adding more groups.");              }              catch (SPException e)              {                Console.WriteLine("AllRolesForCurrentUser failed.");                Console.WriteLine(e.Message);              }            }            finally            {              int i=0;              foreach (string name in names)              {                if (i%100 == 0)                {                  Console.WriteLine("Deleted {0} groups.", i);                }                i++;                web.SiteGroups.Remove(name);              }            }          }        }      }      catch (Exception e)      {        Console.WriteLine(e);      }    }  } }

Richard Willis

Written by

Copyright © 2024 SalamanderSoft Limited