Recently, some of my coworkers and I started to see some customer problems where membership wasn’t populating for users my sites. Membership population issues are complex and difficult to troubleshoot if you’re not sure where to start looking. Part 1 of this blog is intended to cover the basics of how site Membership population to users My Sites work in SharePoint 2007. I’ll add some troubleshooting tips as well. Part 2 will focus on known Site Membership population issues and resolution to those issues.
Intro to Membership
So what is membership? A perfect intro has already been written so here it is:
“Microsoft Office SharePoint Server 2007 supports two types of memberships: Distribution List (DL) memberships and Windows SharePoint Services site memberships. DL membership information is obtained from the Active Directory directory service, and Windows SharePoint Services site membership information is obtained by pulling membership information from the SharePoint site. A user’s public My Site page, called the Profile page, displays the user’s memberships, as well as memberships the user and the viewing user have in common, among other information.”
From:
http://msdn.microsoft.com/en-us/library/ms492573(v=office.12).aspx
I’ll focus on SharePoint site membership since it’s the source of all of the issues I’ve encountered recently. The Profile Synchronization timer job is responsible for synching the site membership information from a content database to the SSP database. It has other tasks but this blog will focus on how this job syncs site membership from Content database to SSP database. This timer job runs hourly by default. A membership webpart resides on a user’s mysite page to display both site and group membership:
The requirements to get membership populated are the following:
Note: This assumes that an SSP is already provisioned, a full crawl has been performed, and users my site is provisioned.
1.) Users must reside in the sites default members group (contribute rights)
2.) Create an extra site group with contribute rights (membership of this group doesn’t matter)
3.) Wait for the Profile Synchronization Timer job to run
Behind the Scenes
Before diving into any troubleshooting, it’s important to understand that there are many moving parts for this single timer job. We leverage SQL tables in both the SSP database and Content database. I won’t cover the stored procedure names and what they do because a Profile Spec document already exists and is available publicly here. This is a high level summary of what are the moving parts and how they work together to ensure a user can view membership on his/her mysite.
Change Log
When starting profile sync, the first thing we do is notify the SSP’s Profile Stats table indicating profile sync has started. Next, a decision is made on whether or not to perform a Full or Incremental Sync. The concept is similar to how changes are detected via an Incremental Crawl (Search). In this case, the last change is recorded from the previous sync in the SSP’s ContentDBSynch table. When ProfileSynch timer job runs, we fetch the latest change from this table. We use that to query changes against the associated Content Databases event cache table looking for changes greater than the change log value. The types of changes queried are adds/removes/changes for objects like sites, users, and groups.
What about New Sites?
If new sites are detected, we register those in the SSP’s SiteSync table.
What about Users?
If changes to users (Adds, Modify, Removes) are collected, we subsequently collect the associated User’s SID and Site ID from the Content databases UserInfo table. The user collected data is finally pushed to the SSP’s UserSites table.
What about Groups/Group Membership?
Groups are pulled from the Content databases SiteGroupsView table. GroupMembership is pulled by a special SQL query which pulls this data from both the UserInfo table and Group Membership table. We create some temp tables within the SSP to store the Group and Group Membership data pulled from the Content DB.
Almost Completed
Groups are added from the associated SSP’s temp table to the SSP’s MemberGroups table. Group Membership information is pulled from the associated SSP Temp table and copied into the SSP’s User Memberships table. Next, the SSP’s ContentDBSync table is updated with the latest change log and the SSP Profile_Stats table is updated to change status to complete. Finally, the membercount column within the membergroup table is updated for the particular groups that have added/removed users.
Initial Troubleshooting
These are some recommended initial troubleshooting steps to perform when site membership isn’t populating for users My Site.
Step 1: Crank down the timer job
Troubleshooting why site membership isn’t populating to one or more users mysite is difficult to troubleshoot. OWStimer process is responsible for making this magic happen via the Profile Synchronization timer job. Waiting hourly for this timer job to run and hoping for the best isn’t the best use of time when it simply doesn’t appear to work. The first thing I would do is crank down how often this timer job runs until you get site membership populating. You can do this with the following command:
stsadm -o sync -synctiming M:1
This causes the Profile Synchronization job to run every minute.
Step 2: ULS log with ULS Viewer
I would also crank up verbose ULS logging for categories Timer and User Profiles in Central Administrator/Operations/Diagnostic Logging. Finally, download ULS viewer from here and monitor the ULS logs real time:
http://code.msdn.microsoft.com/ULSViewer
Note: You may need to hop around to different SharePoint servers in a multi-server farm with ULS Viewer. The server that picks up the scheduled timer job first will run it and you’ll see something like the following in the ULS logs:
09/25/2010 12:20:00.15 OWSTIMER.EXE (0x0740) 0x08C4 Windows SharePoint Services Timer 8e45 Verbose Begin invoke timer job Profile Synchronization, id {C516F71C-0018-40AD-AD9F-13453209DE0F}, DB {EC9FAAE6-29A5-4F6D-9CC2-5E8DCAEC0569}
You can use ULS viewer to filter on the event ID 8e45 within your ULS logs to find out which server kicked this timer job off. Once you find this event, then it’s simply a matter of filtering the ULS log based on the thread ID. In this example, the thread ID is 0x08c4.
Start looking for any error/warnings/critical events that fire until timer job has completed. Once you get to the following trace event, the Profile Sync job has completed:
09/25/2010 12:20:01.97 OWSTIMER.EXE (0x0740) 0x08C4 Windows SharePoint Services Timer 8e46 Verbose End invoke timer job Profile Synchronization, id {C516F71C-0018-40AD-AD9F-13453209DE0F}, DB {EC9FAAE6-29A5-4F6D-9CC2-5E8DCAEC0569}
I’ll cover most of the SQL stored procedure calls you find during the profile synch review in the ULS logs in the advanced troubleshooting section.
Step 3 – Make user group changes on the associated site
Ensure that the user or users that are lacking membership presence in his/her mysite is added to the default sites members group. I would also ensure that particular group has contribute rights.
How do I get there?
1. Access the SharePoint site
2. Select Site Actions, Site Settings
3. Select People and Groups
4. Select More link
5. Should see all groups including the default site name Members group
6. Click on sitename members group “test3 Members” and ensure the particular user is added to this group
7. Click on the edit box next to sitename members group “test3 Members” and ensure contribute right is checked
Also, ensure that a custom created SharePoint group is created and has contribute rights. Finally, trying to toggle the user by doing the following is a valid test:
1. Removing the user from sitemembers group (For Example: test3 Members) 2. Running the profile sync timer job 3. Adding the user back to sitemembers group (For Example: test3 Members) 4. Running the profile sync timer job
This may or may not resolve the issue but this will make more sense why I recommend it after reviewing the advanced troubleshooting section.
Advanced Troubleshooting – (One or two users)
This section is for troubleshooting one or two users. The usual symptom is that that mysite fails to display site membership for one or two users. Every user’s my site displays site membership correctly except for these one or two users.
After the Profile Synch timer job has run
In order to display site membership via mysites, some SQL tables are used to store/retrieve this information. The membergroup table contains site groups that have been sync’d. The UserMemberships table contains the user membership information and references the associated membergroup. To validate that a user\users have properly synched and associated with the appropriate site group, you must query the UserMemberships and Membergroup tables within the SSP database.
For Example, I want to validate the profile synch pushed the Test3 site group to the SSP’s Membergroup table.
select * from Membergroup with(NOLOCK) where DisplayName = ‘test3′
There are other columns but I’m looking for the Id of the particular group which is 9 in this case.
The UserMembership table within the SSP database contains all the users and defines what MemberGroup ID they belong to.
The UserMembership table is defined by a SID and MemberGroupID. In this case, we identify the user by SID so it’s not as simple as locating a user by his/her name. Also, you cannot perform select queries based on SID. Luckily the SID information is located in the Content databases UserInfo table. The following sample SQL query requires the following from you:
The red portions of the query are where you manually insert the three above items.
/*Manually replace SSP_DB with the name of your SSP database*/
USE SSP_DB
/*Replace WSS_Content with the name of your Content database and Replace tp_Login with the specified domain\user*/
select Distinct MemberGroupId from UserMemberships INNER JOIN WSS_Content.dbo.UserInfo ON UserMemberships.SID = WSS_Content.dbo.UserInfo.tp_SystemID and tp_Login = ‘domain\user’
Example Output:
This SQL query will output all of the MemberGroupID’s , “Sites”, that a particular user is a member of.
Question: What does this mean?
Answer: The SSP is aware that the user is assigned as a member of a site group. That is, the user resides in the SSP’s UserMembership table and assigned to the appropriate member group (SiteGroup).
Question: How do I know which Site Groups represent a specific MemberGroupId?
Answer: Run the following SQL query:
/*Manually replace SSP_DB with the name of your SSP database*/ USE SSP_DB
/*Replace ID with the ID’s with the MemberGroupID from the previous Query*/ select Id, DisplayName, Url, MemberCount from MemberGroup with(NOLOCK) where Id = ‘9’ OR Id = ’10’ OR Id = ’11’
Before Profile Synch timer job run
Step 1 – Is the user or group changes available for the Profile Synch Timer job?
The Profile Synch timer job has many moving parts. It acts similar to the way incremental crawls (search) works by keeping track of the latest change synchronized from the Content database. It stores this into the SSP’s ContentDBSynch table. We then perform several queries against the Content Databases event cache table to determine and collect changes like the following:
1) Site Group has been modified (added/deleted)
2) Users have been added/removed to Site Group
Note: This isn’t everything but just to point out a few examples
Run the following to get the latest change ID:
–Script to pull current change token–
/*Replace SharePoint_Config with your Config database name*/ use SharePoint_Config Declare @DB Uniqueidentifier
/*Replace WSS_Content with the name of your content database*/ SELECT @DB = Objects.Id from Objects with(NOLOCK) where Name = ‘WSS_Content’
/*Replace SSP_DB with your SSP database name*/ use SSP_DB select CurrentChangeToken from ContentDBSynch with(NOLOCK) where ContentDBID = @DB
It outputs something similar to the following:
The last 4 digits is the last change SSP is aware of which is 5533. In this example, I want to query all records from event cache table that are greater than 5533 and have the following object types:
- 128 – User
- 1 – Item
- 256 – Group
The following query will confirm that changes were logged to the above three objects with a change greater than 5533.
select * from EventCache with(NOLOCK) where Id > 5533 and ObjectType = ‘128’ or Id > 5533 and ObjectType = ‘1’ or Id > 5533 and ObjectType = ‘256’
The output looks like:
This is good in that new changes are available after you perhaps added some users to SharePoint site groups etc… However, what does this information mean and how can you use it to your advantage? I would test by performing the following:
1. Add a user to the default site membership group. 2. Validate the user appears in eventcache with later
I’ve added a user named Jon to site Test 5’s default members group. I know that the change is greater than 5533 from the previous query.
–Change WSS_Content to the name of your Content database– Use WSS_Content
Declare @USER int
–Change ‘Domain\User’ to the user you tracking– SELECT @USER = tp_id from UserInfo with(NOLOCK) where tp_Login = ‘Contoso\jon’
select * from EventCache with(NOLOCK) where Id > 5533 and ItemId = @USER
Now I have a more scoped output that contain only entries directly related with User Jon that I added J
That’s it for troubleshooting prior to Profile Synch timer job run for one or two users. It’s important to validate that the changes are available to be picked up by the Profile Synch timer job prior to runtime. It’s equally important to ensure the SSP has logged membership correctly for individual users. We do have a couple of known issues with this specific symptom to be aware of which will be in Part 2 in this blog series.
Thanks!
Russmax