Thought it high time I blog this. You may get errors like this following a DB move, for example, moving a content db from a sql box to a cluster / vice versa <grin>.

7888:
A runtime exception was detected. Details follow.
Message: Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.
The statement has been terminated.
Techinal Details:
System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.
The statement has been terminated.

5553:
failure trying to synch site [SITEGUID] for ContentDB [CONTENT DB GUID] WebApp [WEB APP GUID].  Exception message was Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.
The statement has been terminated.

Lots of diving off into the logs looking at the Timer and then back into SQL to see what syncs where going on revealed whats going on – I should have run preparetomove on the content db before moving it 🙂 – hence:

WHY:

This occurs when a site collection or a content database is moved from one web application to another without running the Preparetomove command first. Without running the Preparetomove command, the SSP thinks the site is new and tries to insert data into the UserMemberships table that already exists there. This causes SQL to throw a duplicate key error and causes the profile synchronization job to fail.

1. Open a command prompt and navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN
2. Run ‘stsadm- o preparetomove -Site <http://whatevertheguiltysiteis/> -OldContentDb <GUID of the contentdb listed in event log error>’
3. Run ‘stsadm -o sync -deleteolddatabases 0’

NOTE:
To get the Site relevant to the GUID you can obtain the content DB name which if you then look inside Application Management Content DB you will get an answer:
To do this I simply used a SQL script as follows:

SELECT Id, ClassId, ParentId, Name, Status, Version, Properties
FROM Objects
WHERE (Id = ‘The GUID from Event ID 5553’)

Hope this helps!