Wednesday, March 31, 2010

Error in the Site Data Web Service - No results returned from Search

I found this error message in the Crawl Log of Search Administration settings in Central Administration site after getting a complaint from the user that they cannot find any results when performing a search on a particular site collection.  It happened after we performed a site migration from one SharePoint 2007 farm to another SharePoint 2007 farm running on two different domains with trust relationship.

The root cause of this error is corrupted SIDs in the UserInfo table of the site collection, which returns no search results.  And the steps to solve the issue are:

1. Log on to the database server of the SharePoint farm and open SQL Server Management Studio

2. Locate the content database containing the site collection

3. Open SQL query windows and execute the following query against the content database
Select tp_login, tp_systemid, tp_deleted from userinfo where len(ltrim(rtrim(tp_systemid))) <25 and tp_deleted = 0

4. Note down a list of users returned from the query, other than the following system users, if any
  • NT AUTHORITY\authenticated users
  • NT AUTHORITY\local service
  • NT AUTHORITY\system
  • SHAREPOINT\system
5. Grant a permission (e.g. Read permission) to those users (except the system users listed above) in the root site of the site collection.  This will somehow fix the corrupted SID.  After that, can also remove back the assigned user permission if needed.
6. Reexecute the query againsts the content database until no more users (other than the system users), are returned from the query. 
7. Run the incremental or full crawl on the content source containing the site collection
8. Perform the search on the site collection and get the results

Notes: When performing step 5, I found that some users cannot be granted the permission.  It returned an error message, saying "The user does not exist or is not unique".  I just skipped these users and move on to the next user in the list. 


  1. Thanks, David. You have resolved the issue.
    Just wondering, are those skipped users resolved too?

  2. The skipped users seem to be no longer exist in Active Directory.

  3. Hi Dave
    Here is my list of accounts:
    NT AUTHORITY\authenticated users
    Any ideas why I have 3 sharepoint\system accounts?

  4. I ran this on SharePoint 2010 and readded the groups (not from AD but rather groups that were created by the Membership provider) to the root site collection. When I refreshed the query the groups were still there. The sites were migrated from 2007 via a database attach upgrade method. Any ideas why these groups persist? Could thay be the groups that were added under the old provider which we replaced when we moved to 2010?