SharePoint: Person or Group column does not display expected results when limited to a SharePoint group
Consider the following scenario:
You have a SharePoint list with a Person or Group column.
This column is limited to choose from a SharePoint group called (for example) Approvers.
Within this SharePoint group, you have three users with (for example) first name Jeff, and one user with last name Jefferson.
Within the person or group column, you type “Jeff” into the people picker control to find the users.
The results may show “No results found” or maybe just one or two of the three users in the Approvers group that contain the string “jeff”, whereas you probably expect all four users to be returned.
Note: If you keep typing and include the first letter of the users last name, it finds the applicable users correctly.
Cause:
This behavior occurs because you have more than 31 users in your Active Directory that contain the string “jeff”. This could be part of their first name, last name, or account name.
Details:
When you have a person or group column set to choose from a certain SharePoint group, it works like this:
- SharePoint still searches Active Directory (AD) first for the given search term ex: “Jeff”.
- We accept a maximum of 31 results from AD.
- We then compare the AD results with the users that are in the SharePoint group ex: “Approvers”.
- Any AD results that match results in the SharePoint group will be displayed as suggestions in the people picker control.
Based on the functionality above, we can conclude a few things:
- If not all “Jeff” users in the Approvers SharePoint group are within the first 31 results we get from AD, they will not be displayed.
- More common search terms like “Jeff” will return more AD results, making it less likely that the 31 AD results will match anyone in the SharePoint group.
- More distinct terms like a users last name will work better.
- Essentially, for a search term to work 100% of the time, there must be 31 or fewer matches for that term in all of your trusted Active Directory domains — or at least all the domains that People Picker is currently set to query.
Notes:
This behavior is certainly “by design” ie: the Source Code shows that we do this in the manner explained above on purpose.
The behavior is the same in both SharePoint 2013 and SharePoint 2016.
What can be done?
The most obvious workaround is to just type more. The more letters you type of the users first and last name, the more distinct the query becomes and the more likely it is to return less than 31 results from AD.
It’s also possible to tune your People Picker settings at the web application level to exclude unnecessary domains or otherwise limit the results you get from Active Directory. However, keep in mind that that type of change is web app-wide and could negatively impact people picker functionality in other site collections.
You could also change the person or group column to search from “all users” instead of a SharePoint group, but that may not align well with what you’re using that column for.
Tech stuff:
For those that are interested, this is what we see in the SharePoint ULS logs when they’re turned up to Verbose:
Note: to find the proper request, you can search the logs for “ClientPeoplePicker”, or even search for the people picker search term that was used ex: “jeff”.
It will be a POST to the client-side object model (CSOM) endpoint: /_vti_bin/client.svc/ProcessQuery
— The CSOM request looks like this:
11/29/2017 08:43:02.85 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation CSOM afxv6 Verbose CSOM Request XML: <Request xmlns=”http://schemas.microsoft.com/sharepoint/clientquery/2009″ SchemaVersion=”15.0.0.0″ LibraryVersion=”15.0.0.0″ ApplicationName=”Javascript Library”><Actions><StaticMethod TypeId=”{de2db963-8bab-4fb4-8a58-611aebc5254b}” Name=”ClientPeoplePickerSearchUser” Id=”0″><Parameters><Parameter TypeId=”{ac9358c6-e9b1-4514-bf6e-106acbfb19ce}”><Property Name=”AllowEmailAddresses” Type=”Boolean”>false</Property><Property Name=”AllowMultipleEntities” Type=”Boolean”>false</Property><Property Name=”AllUrlZones” Type=”Boolean”>false</Property><Property Name=”EnabledClaimProviders” Type=”String”></Property><Property Name=”ForceClaims” Type=”Boolean”>false</Property><Property Name=“MaximumEntitySuggestions” Type=”Number”>30</Property><Property Name=”PrincipalSource” Type=”Number”>15</Property><Property Name=”PrincipalType” Type=”Number”>1</Property><Property Name=”QueryString” Type=”String”>jeff</Property><Property Name=”Required” Type=”Boolean”>false</Property><Property Name=”SharePointGroupID” Type=”Number”>28</Property><Property Name=”UrlZone” Type=”Number”>0</Property><Property Name=”UrlZoneSpecified” Type=”Boolean”>false</Property><Property Name=”Web” Type=”Null” /><Property Name=”WebApplicationID” Type=”String”>{00000000-0000-0000-0000-000000000000}</Property></Parameter></Parameters></StaticMethod></Actions><ObjectPaths /></Request> 80a6319e-5802-0012-0b83-18899d030ac8
— Start of the request:
11/29/2017 08:43:02.86 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Web Controls agpxv Verbose ClientPeoplePicker: Searching for user in Claims Mode: jeff 80a6319e-5802-0012-0b83-18899d030ac8
— LDAP Query to Active Directory:
11/29/2017 08:43:02.86 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Performance ftq1 Verbose SearchFromGC name = contoso.com. start 80a6319e-5802-0012-0b83-18899d030ac8
— Then there’s 31 of these GetAccountNameFromSid pairs:
11/29/2017 08:43:02.88 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Performance ftq4 Verbose GetAccountNameFromSid “0x010500000000000515000000EE5E5C6562E040CEE60D7FD20E070000” start 80a6319e-5802-0012-0b83-18899d030ac8
11/29/2017 08:43:02.88 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Performance ftq5 Verbose GetAccountNameFromSid “0x010500000000000515000000EE5E5C6562E040CEE60D7FD20E070000” returned. returnValue=True 80a6319e-5802-0012-0b83-18899d030ac8
— LDAP query done. We got 31 results.
11/29/2017 08:43:02.97 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Performance ftq2 Verbose SearchFromGC name = contoso.com. returned. Result count = 31 80a6319e-5802-0012-0b83-18899d030ac8
— Then we query the content database for the SharePoint group membership. In this case, it’s the “Approvers” group, which has group id 28.
11/29/2017 08:43:02.97 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Database b6p4 VerboseEx SqlCommand: ; EXEC proc_seclistsitegroupmembership ‘157483bc-53b5-4372-ae62-b7a5de6e4926’, 28, 1, 1, 1, 1, 1, @RequestGuid OUTPUT 80a6319e-5802-0012-0b83-18899d030ac8
11/29/2017 08:43:02.97 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Database tzkv Verbose SqlCommand: ‘proc_SecListSiteGroupMembership’ CommandType: StoredProcedure CommandTimeout: 0 Parameter: ‘@RETURN_VALUE’ Type: Int Size: 0 Direction: ReturnValue Value: ” Parameter: ‘@SiteId’ Type: UniqueIdentifier Size: 0 Direction: Input Value: ‘157483bc-53b5-4372-ae62-b7a5de6e4926’ Parameter: ‘@GroupId’ Type: Int Size: 0 Direction: Input Value: ’28’ Parameter: ‘@CurrentUserId’ Type: Int Size: 0 Direction: Input Value: ‘1’ Parameter: ‘@SiteAuditor’ Type: Bit Size: 0 Direction: Input Value: ‘True’ Parameter: ‘@BelongsToGroup’ Type: Bit Size: 0 Direction: Input Value: ‘True’ Parameter: ‘@GroupOwnerId’ Type: Int Size: 0 Direction: Input Value: ‘1’ Parameter: ‘@CurrentUserIsOwner’ Type: Bit Size: 0 Direction: Input Value: ‘True’ Parameter: ‘@RequestGuid’ Type: UniqueIdentifier Size: 0 Direction: Input Value: ’80a6319e-5802-0012-0b83-18899d030ac8′ 80a6319e-5802-0012-0b83-18899d030ac8
— From the stack, we see this coming from PeopleEditor.ArrPickerEntityFilterBySPUserCollection, so we know it’s people picker that is issuing this query.
11/29/2017 08:43:02.97 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Database tzkk VerboseEx SqlCommand StackTrace-Managed: at Microsoft.SharePoint.Utilities.SqlSession.OnPreExecuteCommand(SqlCommand command) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock) at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetUsersDataAsSafeArray(String bstrUrl, UInt32 dwUsersScope, UInt32 dwUserCollectionFlags, String bstrValue, UInt32 dwValue, UInt32& pdwColCount, UInt32& pdwRowCount, Object& pvarDataSet) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetUsersDataAsSafeArray(String bstrUrl, UInt32 dwUsersScope, UInt32 dwUserCollectionFlags, String bstrValue, UInt32 dwValue, UInt32& pdwColCount, UInt32& pdwRowCount, Object& pvarDataSet) at Microsoft.SharePoint.Library.SPRequest.GetUsersDataAsSafeArray(String bstrUrl, UInt32 dwUsersScope, UInt32 dwUserCollectionFlags, String bstrValue, UInt32 dwValue, UInt32& pdwColCount, UInt32& pdwRowCount, Object& pvarDataSet) at Microsoft.SharePoint.SPUserCollection.InitUsersCore(Boolean fCustomUsers, String[] strIdentifiers, SPUserCollectionFlags ucf) at Microsoft.SharePoint.SPBaseCollection.GetEnumerator() at Microsoft.SharePoint.SPUserCollection.TryGetIdByLogin(String loginName) at Microsoft.SharePoint.WebControls.PeopleEditor.ArrPickerEntityFilterBySPUserCollection(PickerEntity[] entities, SPUserCollection spuc) at Microsoft.SharePoint.WebControls.ClientPeoplePicker.SearchClaimsEntityCore(ClientPeoplePickerQueryParameters qParams) at Microsoft.SharePoint.WebControls.ClientPeoplePicker.SearchClaimsEntity(ClientPeoplePickerQueryParameters qParams) at Microsoft.SharePoint.WebControls.ClientPeoplePicker.SearchEntity(ClientPeoplePickerQueryParameters qParams) at Microsoft.SharePoint.WebControls.ClientPeoplePicker.ClientPeoplePickerSearchUser_Client(ClientPeoplePickerQueryParameters queryParams) at Microsoft.SharePoint.ApplicationPages.ClientPickerQuery.ClientPeoplePickerWebServiceInterface.ClientPeoplePickerSearchUser(ClientPeoplePickerQueryParameters queryParams) at Microsoft.SharePoint.ServerStub.ApplicationPages.ClientPeoplePickerWebServiceInterfaceServerStub.InvokeStaticMethod(String methodName, XmlNodeList xmlargs, ProxyContext proxyContext, Boolean& isVoid) at Microsoft.SharePoint.Client.ServerStub.InvokeStaticMethodWithMonitoredScope(String methodName, XmlNodeList args, ProxyContext proxyContext, Boolean& isVoid) at Microsoft.SharePoint.Client.ClientMethodsProcessor.InvokeStaticMethod <truncated> 80a6319e-5802-0012-0b83-18899d030ac8
— And then we’re done with the search. We don’t actually log anything about comparing the 31 AD results with the Approvers group members.
11/29/2017 08:43:02.97 w3wp.exe (0x2068) 0x39A0 SharePoint Foundation Web Controls agpxm Verbose ClientPeoplePickerWebServiceInterface: End – Search user: jeff 80a6319e-5802-0012-0b83-18899d030ac8
Summary:
We got a max of 31 results from AD for “jeff”.
Since only one of my “Jeff” users in the Approvers SharePoint group are within those first 31 results, it returns only the single result to the people picker control.
