Sharepoint Active Directory Migration - SID's

Ever migrate your SharePoint site to a totally new environment and discover that your efforts to re-create your Active Directory were all for nothing, since all the users got new SIDs? Symptoms like: The administrator of the server can log in, but nobody else can, even though you're SURE their usernames and passwords are right.

In this solution you will be making a modification to a SharePoint 2007/2010 database.  It is erroneously reported all over the internet that if you make a change to a SharePoint database you will loose all support from Microsoft.  That simply isn’t true – Here is the verbiage straight from Microsoft:

If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:
• Perform a database restoration from the last known good backup that did not include the database modifications
• Roll back all the database modifications

Ok, disclaimer over - here's a SQL query that'll fix that up for you in a one step. Open Query Analyzer and run it against the content database for your site, and it will update all the SIDs for your users to the SID that is reported for that user by Active Directory.

DECLARE @login varchar(40), @systemid varbinary(128)

SELECT tp_login, tp_systemid FROM userinfo where tp_deleted = 0

OPEN curUsers

	FETCH NEXT FROM curUsers INTO @login, @systemid

	PRINT 'Resetting user ' + @login + ' to new SID '
	PRINT suser_sid(@login)
	UPDATE UserInfo
		SET tp_systemid = suser_sid(tp_login) WHERE CURRENT OF curUsers
	FETCH NEXT FROM curUsers INTO @login, @systemid

CLOSE curUsers


Permalink | Comments (0) | Post RSSRSS comment feed