Advertisements

Using PowerShell (and SQL) to Copy Users from SharePoint 2007 to 2010


imagePowerShell is a neat tool. There. I said it. What, you already knew that? Oh well… I still think it’s cool. You may have guessed, that due to recent posts (and a few yet to be posted), focused around PowerShell, I’ve been using it quite a bit. Today’s post is on how to get users out of your 2007 environment, and into 2010, specifically around FBA users.

I’ve been working with a new client recently, and we are migrating a 2007 based internet site to a 2010 based internet site, which has lots of FBA users. Now, I will not name the tool directly, but, with a certain migration tool, I spent the past week jumping through a lot of hoops to migrate the users over to 2010. A LOT of hoops. And no matter what we attempted to do, we just couldn’t get the users populated into the new 2010 site.

So, I rolled up my sleeves today and managed to complete the task in a short while… here’s what I did.

First, on the 2007 server, let’s call it SQL07.company.com, I went into SQL, and performed the following query to get the list of site collections within the content database of the web application in question. Luckily, there was only one site collection, so there was not much of a list here, but, I did need to get the GUID of the site collection, so, I did this:

image

Now that I had the site collection GUID, it was time to extract the users so we could add them to our 2010 server, which was some simple SQL code to build up the strings necessary. Now, I could have written more complex code, but, this just needed to get done (the select query is wrapped to display it all here).

   1: SELECT 'New-SPUser -UserAlias "' 

   2:     + REPLACE(tp_Login, 'acaspnetsqlmembershipprovider:', 'i:0#.f|sql-membershipprovider|') 

   3:     + '" -Web http://internet -DisplayName "' 

   4:     + tp_Title + '" -Email "' 

   5:     + tp_Email + '"'

   6: FROM UserInfo 

   7: WHERE tp_SiteID = 'CB14D956-9DE3-4A32-BD0A-155E93A663AA'

   8: AND tp_Login LIKE 'acaspnetsqlmembershipprovider%'

More information on the New-SPUser cmdlet in SharePoint 2010 can be found here: http://technet.microsoft.com/en-us/library/ff607868.aspx

And I switched the results view to Text in the query window in SQL, copied and pasted that into a text file, with a .ps1 extension, and called it from my SharePoint 2010 Administration Console (PowerShell), and off went the quick addition of thousands of FBA users into our new environment!

Now, what you will see here is a simple replace mechanism as well, for swapping all acaspnetsqlmembershipprovider: prefixed FBA users, to the new claims based format in 2010, i:0#.f|sql-membershipprovider|. The AND clause in the above SQL statement also only searches for FBA users, and not users out of the AD domain.

If you need to figure out what your prefix should be, add an FBA user to your site, and have a look at their profile page, it should list a funky username, that looks like the above: i:0#.f|membershipprovidername|.

Simple. Easy. And extremely effective.

Advertisements

About Geoff Varosky
Geoff Varosky is a Senior Architect for BlueMetal Architects, based out of Watertown, MA. He has been architecting and developing web based applications his entire career, and has been working with SharePoint for the past 13 years. Geoff is an active member of the SharePoint community, Co-Founder and Co-Organizer of the Boston Area SharePoint Users Group, co-founder for the Boston Office 365 Users Group, co-organizer for SharePoint Saturday Boston and speaks regularly at SharePoint events and user groups.

5 Responses to Using PowerShell (and SQL) to Copy Users from SharePoint 2007 to 2010

  1. Pingback: SharePoint Daily

  2. Pingback: SharePoint 2010: Recopilatorio de enlaces interesantes (XIV)! « Pasión por la tecnología…

  3. Pingback: Blog del CIIN

  4. shobs says:

    I get the error New-SPUser -UserAlias “” -Web http://intranet. -DisplayName “Userxxxx” -Email”Userxxxx@gmail.com”

    TerminatorExpectedAtEndOfString

    • shobs – are you getting this from SQL, or, from PowerShell?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: