About Me · Send mail to the author(s) E-mail · Twitter

At GROSSWEBER we practice what we preach. We offer trainings for modern software technologies like Behavior Driven Development, Clean Code and Git. Our staff is fluent in a variety of languages, including English.

Feed Icon


Open Source Projects


Blogs of friends

Now playing [?]

Error retrieving information from external service.

Migrating SharePoint Content Databases To A New Farm While Keeping Security Settings

Posted in .NET | SharePoint | SQL Server at Saturday, 25 April 2009 16:40 W. Europe Daylight Time

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)

SELECT tp_Login, tp_SystemID FROM UserInfo WHERE tp_Deleted = 0

OPEN curUsers
FETCH NEXT FROM curUsers INTO @login, @systemid

    DECLARE @newLoginName AS nvarchar(255) = REPLACE(REPLACE(@login, 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search')
    DECLARE @newSID varbinary(85) = SUSER_SID(@newLoginName)
        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
    FETCH NEXT FROM curUsers INTO @login, @systemid

CLOSE curUsers

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')