The server this blog is hosted on was upgraded recently, i.e. is now run on a new dedicated server. Actually, next to this blog there’s a lot more going on. We, a bunch of geeks, are self-hosting mail, web sites, blogs and some collaboration tools like SharePoint (Windows SharePoint Services, in our case). Yeah, SharePoint, a true beast in and of itself. I can’t tell you how much I do not miss developing software for it and setting up customer sites.
I wanted to make the move to the new server as smooth as possible for our SharePoint users. Because we do not use Active Directory to authenticate our users, we obviously had to migrate the SharePoint user accounts manually. That is, re-create each user on the new server giving them a random password and communicate the change.
Moving a SharePoint site is surprisingly pretty well documented on TechNet, but won’t tell you about one important aspect: When you move the site to a new farm and the site does not use Active Directory, you will have to set up security anew.
Why? Because SharePoint matches user accounts by their SID, a value that is unique for each user account, even across machines: OLDMACHINE\foo’s SID is different from NEWMACHINE\foo’s SID. Burdening the four site collection administrator with this task is simply a no-go.
During my research how to work around that I found the Dustin Miller’s excellent post “Fix those SIDs”. It describes the process of massaging a SharePoint site collection database to replace old SIDs with the account SIDs of the current machine. I’ve extended it a bit, because I also decided to rename the SharePoint Search account while moving to the new server (note the extra REPLACE in line 11).
DECLARE @login nvarchar(255), @SystemId varbinary(512)
DECLARE curUsers CURSOR LOCAL FOR
SELECT tp_Login, tp_SystemID FROM UserInfo WHERE tp_Deleted = 0
OPEN curUsers
FETCH NEXT FROM curUsers INTO @login, @systemid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @newLoginName AS nvarchar(255) = REPLACE(REPLACE(@login, 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search')
DECLARE @newSID varbinary(85) = SUSER_SID(@newLoginName)
IF @newSID IS NOT NULL
BEGIN
PRINT 'Resetting user ' + @login + ' to new login ' + @newLoginName + ' with SID '
PRINT SUSER_SID(@newLoginName)
UPDATE UserInfo
SET tp_SystemID = SUSER_SID(@newLoginName),
tp_Login = @newLoginName
WHERE CURRENT OF curUsers
END
FETCH NEXT FROM curUsers INTO @login, @systemid
END
CLOSE curUsers
DEALLOCATE curUsers
GO
After the script ran, take a look at the UserInfo table and SELECT rows that still contain OLDMACHINE in the tp_Login column. This helps you get a quick overview of what accounts have been missed during account re-creation.
As an extra step, I found it appropriate to update the site’s user entry as well (the account name that shows up in the site’s user list when no full name is given) to reflect the new machine name.
UPDATE [AllUserData]
SET [nvarchar1] = REPLACE(REPLACE([nvarchar1], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search'),
[nvarchar2] = REPLACE(REPLACE([nvarchar2], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search'),
[nvarchar3] = REPLACE(REPLACE([nvarchar3], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search')