Second Life of a Hungarian SharePoint Geek

May 13, 2011

‘User cannot be found’ error solved using a SQL query

Filed under: Administration, Bugs, Content database, SP 2010 — Tags: , , , — Peter Holpar @ 21:23

A few days ago I created a new web application on one of our servers, and just was to check back if I set the right user as the site owner, but clicking the Change site collection administrators link in central admin displayed the following error message:
 image

In the SharePoint log I found the corresponding lines:

05/12/2011 12:21:24.94     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Logging Correlation Data          xmnv    Medium      Name=Request (GET:http://adminsite/_admin/owners.aspx)    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.94     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Logging Correlation Data          xmnv    Medium      Site=/    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.95     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Database                          4ohp    High        Enumerating all sites in SPWebApplication Name=Customerext.    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.95     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Database                          4ohq    Medium      Site Enumeration Stack:    at Microsoft.SharePoint.SPBaseCollection.GetEnumerator()     at Microsoft.SharePoint.WebControls.SiteAdministrationSelector.DefaultSelectionId()     at Microsoft.SharePoint.WebControls.ContextSelector`1.OnLoad(EventArgs e)     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)     at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBefore…    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.95*    w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Database                          4ohq    Medium      …AsyncPoint, Boolean includeStagesAfterAsyncPoint)     at System.Web.UI.Page.ProcessRequest()     at System.Web.UI.Page.ProcessRequest(HttpContext context)     at ASP._admin_owners_aspx.ProcessRequest(HttpContext context)     at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)     at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)     at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)     at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelpe…    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.95*    w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Database                          4ohq    Medium      …r(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)      01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.97     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             General                           72nz    Medium      Videntityinfo::isFreshToken reported failure.    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.98     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Runtime                           tkau    Unexpected    Microsoft.SharePoint.SPException: User cannot be found.    at Microsoft.SharePoint.SPUserCollection.GetByID(Int32 id)     at Microsoft.SharePoint.Administration.SPSiteAdministration.get_SecondaryContactLoginName()     at Microsoft.SharePoint.ApplicationPages.OwnersPage.OnContextChange(Object sender, EventArgs e)     at Microsoft.SharePoint.WebControls.ContextSelector`1.OnContextChange(EventArgs e)     at Microsoft.SharePoint.WebControls.ContextSelector`1.set_CurrentId(String value)     at Microsoft.SharePoint.WebControls.ContextSelector`1.OnLoad(EventArgs e)     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at Sys…    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.98*    w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Runtime                           tkau    Unexpected    …tem.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Control.LoadRecursive()     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    01740b25-3356-433e-85af-49d6c90bd9bb
05/12/2011 12:21:24.98     w3wp.exe (0x19DC)                           0x12A0    SharePoint Foundation             Monitoring                        b4ly    Medium      Leaving Monitored Scope (Request (GET:http://adminsite/_admin/owners.aspx)). Execution Time=54.1751647921174    01740b25-3356-433e-85af-49d6c90bd9bb

It suggested that there should be a non-existing user set as the secondary admin of the site. For example, a user was set as the (secondary) site admin and that user was deleted later on, before being replaced on that role.

I’ve discussed the issue with the site operators, and they told me that most of the sites on the server were migrated from a MOSS 2007 server, and probably it caused the mismatch of the user IDs.

I found the following (MOSS 2007) resources on the web when looking for a confirmation of my theory:

Error message when you try to manage a site collection in Windows SharePoint Services 3.0 or in SharePoint Server 2007: "User cannot be found"

User cannot be found error

I’ve checked the code that caused the issue and found that getters of both the Owner and SecondaryContact properties of the SPSite class contains a call like this:

this.RootWeb.SiteUsers.GetByID(this.m_OwnerID)

Of course, for the SecondaryContact  the m_nSecondaryContactID field is passed as parameter.

The GetByID method of the SPUserCollection class throws an exception if the specified user is not found in the collection.

Side note: GetByID method calls the internal GetByIDNoThrow method that does not throw any exception if the user specified by the ID is not found. This latter method calls forward to the private FindUserNoThrow method that is able to find the position of a user in the collection based on a property specified by the nested internal enum UserFields and an explicit value to compare against. This method has two overloads, one for String-based, and another one for int-based properties. The method returns the result of the search as a bool value, and the position of the first match as an out uint parameter. Using this value, one can get the corresponding SPUser object through the this[int index] indexer of SPUserCollection.

Possible SPUser properties to search for in the SPUserCollection using the FindUserNoThrow method and the UserFields enum:

ID, Sid, Name, LoginName, Email, Notes, IsSiteAdmin, IsDomainGroup, Flags, Max

But let’s back to the topic. I consider this behavior of the site collection admin page to be a bug, and would expect this internal exception to be handled and displayed for end users on the UI to enable them to resolve the issue there.

Since there are a lot of web applications with several site collections on the server, it was the first and probably the hardest part to identify the ones that have incorrect site collection admin settings. In this post I show you a way that might be not supported, but for an administrator probably the quickest one, and that is a query against the SharePoint configuration and content databases.

In the query (that assumes the configuration database is called SharePoint_Config) we first create a SQL cursor and looks up all the content databases, then queries that content DBs one by one to check the site collection settings and existing users. In the query we check both for physically deleted user records, and logically deleted ones (where the UserInfo record exist, but its tp_Deleted field is set to 1).

The query results are aggregated into a temp table, that is display at the end of the query, and is finally dropped.

  1. USE SharePoint_Config
  2.  
  3. CREATE TABLE ##SiteAdmins (
  4. DBName NVARCHAR(200),
  5. SiteId UNIQUEIDENTIFIER,
  6. SiteInfo NVARCHAR(200),
  7. OwnerID INT,
  8. PrimaryAdmin NVARCHAR(255),
  9. PrimaryAdminDeleted INT,
  10. SecondaryContactID INT,
  11. SecondaryAdmin NVARCHAR(255),
  12. SecondaryAdminDeleted INT,
  13. )
  14.  
  15. DECLARE @contentDbName NVARCHAR(200);
  16.  
  17. DECLARE contentDbCursor CURSOR FOR
  18. SELECT [Name] FROM [Objects]
  19. WHERE Properties LIKE '<object type="Microsoft.SharePoint.Administration.SPContentDatabase, Microsoft.SharePoint%'
  20.  
  21. OPEN contentDbCursor
  22.  
  23. FETCH NEXT FROM contentDbCursor
  24. INTO @contentDbName
  25.  
  26. WHILE @@FETCH_STATUS = 0
  27. BEGIN
  28.  
  29. DECLARE @cmd AS VARCHAR(2000)
  30.  
  31. SET @cmd = 'USE [' + @contentDbName + ']
  32. INSERT INTO ##SiteAdmins
  33. (DBName, SiteId, SiteInfo, OwnerID, PrimaryAdmin, PrimaryAdminDeleted,  SecondaryContactID, SecondaryAdmin, SecondaryAdminDeleted)
  34. SELECT
  35.   ''' + @contentDbName + ''' AS DBName,
  36.   S.Id AS SiteId,
  37.   ''/'' + W.FullUrl + '' ('' + W.Title + '')'' AS SiteInfo,
  38.   S.OwnerID,
  39.   CASE
  40.       WHEN S.OwnerID IS NULL THEN ''Not specified''
  41.       ELSE ISNULL(PA.tp_Title, ''Unknown user'')
  42.   END AS PrimaryAdmin,
  43.   CASE WHEN
  44.     (S.OwnerID IS NOT NULL AND PA.tp_Title IS NULL)
  45.     OR PA.tp_Deleted = 1 THEN 1
  46.     ELSE 0
  47.   END AS PrimaryAdminDeleted,
  48.   S.SecondaryContactID,  
  49.   CASE
  50.       WHEN S.SecondaryContactID IS NULL THEN ''Not specified''
  51.       ELSE ISNULL(SA.tp_Title, ''Unknown user'')
  52.   END AS SecondaryAdmin,
  53.   CASE WHEN
  54.     (S.SecondaryContactID IS NOT NULL AND SA.tp_Title IS NULL)
  55.     OR SA.tp_Deleted = 1 THEN 1
  56.     ELSE 0
  57.   END AS SecondaryAdminDeleted
  58. FROM Sites S
  59. LEFT JOIN UserInfo PA ON PA.tp_ID = S.OwnerID AND PA.tp_SiteID = S.Id
  60. LEFT JOIN UserInfo SA ON SA.tp_ID = S.SecondaryContactID AND SA.tp_SiteID = S.Id
  61. LEFT JOIN Webs W ON W.Id = S.RootWebId AND W.SiteId = S.Id'
  62.  
  63. EXECUTE(@cmd)
  64.  
  65. FETCH NEXT FROM contentDbCursor
  66.     INTO @contentDbName
  67. END
  68.  
  69. CLOSE contentDbCursor
  70. DEALLOCATE contentDbCursor
  71.  
  72. SELECT DBName, SiteId, SiteInfo, OwnerID, PrimaryAdmin, PrimaryAdminDeleted, SecondaryContactID, SecondaryAdmin, SecondaryAdminDeleted FROM ##SiteAdmins
  73.  
  74. DROP TABLE ##SiteAdmins

If there is a primary or secondary admin that is displayed as deleted by the script, it must be handled to correct the ‘User cannot be found’ error.

Although you can correct these entries directly in the content DB, either manually or even by extending the above script to update the OwnerID or SecondaryContactID field to a predefined value (like 1 that means the first user in the site or null to no user specified), it is strongly recommended to use a supported tool at least for the data modification part. Such tool might be the STSADM command:

stsadm.exe -o siteowner -url Url -ownerlogin DOMAIN\name -secondarylogin DOMAIN\name

or you can set the Owner and SecondaryContact property of the erroneous SPSite from PowerShell or C#.

You can read a full managed solution in my next post.

Advertisements

1 Comment »

  1. BTw first of all thanks for your post it makes some light for me 🙂 i am finiding the solution since last 4 days now at the end i found the problem. Next is for resolving this issue i I put the 1 directly to the database but still it’s showing unknown and deleted in the ##SiteAdmins

    Comment by Rashid — June 14, 2015 @ 14:08


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: