User Information List (UAL) Synchronization

NOTE: This is a repost from my original blog while on civilian deployment at the Multi-National Corps – Iraq Headquarters and Command in support of Operation Iraqi Freedom.

Each and every site collection leverages a list called the, “User Information List”.  Each and every site collection.  On this list, is the names of the folks with access to that site/collection and what level of access they have. You can view that list, on each individual site collection, by going to:

Simple View

Detailed View

You can also edit that list in SharePoint Designer, navigate to URL/sites/site collection/_catalogs/users (User Information List)/ You may need to modify the permissions, do so by Right Click On Users (User Information List) -> Properties ->Security Tab ->Click Manage Permissions using the browser **NOT RECOMMENDED – BAD JUJU**


From the MOSS 2007 side of things, are the profiles and profile imports. The data between the ‘MOSS User Profile’ and the ‘WSS User Information List’ are bilaterally synchronized. For the most part, this process is very transparent to the administrator; until there is a problem. 

From my personal and ongoing experience with this issue, I’ve noticed commonalities between my site collections synchronizing just peachy and my site collections that don’t synchronize. Again, these are only my experiences both in production and during issue duplication efforts on a developement farm. They are:

1. Site Collections broken out (moved) into new content databases and/or migrated by third party tools such as Metalogix, etc.
2. Site Collections broken out (moved) via stsadm -o mergecontentdb (syntax missing -preparetomove)
          A. In addition, this command has documented issues, please See KB 969242 (At the time of this revision, the -mergecontentdb command has been fixed in SP) 
          B. Database corruption
3. Site Collections renamed, i.e. ‘ACME Day Shift’ to ‘ACME Night Shift’ versus building out a new site collection to begin with or renaming via improper method
4. Corrupt databases not generating event id records and/or fail to appear the Event Application Log or SQL Logs (suspect pages table has records, however they do not appear in the application log; check SQL Suspect Pages table for each database
5. Content databases not detached properly from MOSS and SQL prior to migration from one storage device to another.
6. Content databases, that at least one in time, experienced a full transaction log and users were unable to add, delete or modify information if their site collection fell within that Content Database.

Again, my farm and my issues. The list of 6 above are how I was able to duplicate the synchronization error, and the farm was rebuilt with new content databases after each time and each item was duplicated twice out of four attempts. You may not be able to fix the issue, however perhaps if any of the above apply to you, you can avoid it the next time.  Yet, just randomly putting this out there.

SOLUTION 1: There is a third-party application that can synchronize the ‘User Information List’ with the ‘Profile Database’ Information, it is called Bamboo Solutions – User Profile Sync – v1.4

Unfortunately I refuse to pay for the web part, I prefer to figure out an OTB solution. If anyone finds one, please let me know.

SOLUTION 2: Another thing you can do is query the content database where the site collection resides in and modify the values you need to. I think it is safe to say that this process will negatively affect the supportability of your product, so do so at your own risk.

1. Backup your content database before you begin.
2. Query against or select the content database the site collection resides in.

UPDATE SET = ‘Desired value of field’
WHERE = ‘search value’

For example, if I wanted to change the display name for someone who is appearing as “ACME\mojo.jojo” in a site collection residing within Main_Content_DB. You would run this query against Main_Content_DB:

–WARNING! You must include the WHERE statement
–to avoid changing all fields in the column to
–the ‘Desired value of field’

SET tp_Title = ‘JoJo, Mr. Mojo’
WHERE tp_Login = ‘ACME\mojo.jojo’;

Anyhow, you’ll want to include all apostrophes within each statement, the semi-colon, and again make sure you include the WHERE statement. And always remember, you are never to edit a SharePoint Content Databases; as this will negatively affect the supportability of your product.

As always, if it worked fine yesterday but doesn’t today, BounceTheBox!
-Bill Beehner