Move users / roles / passwords to new core database

Question

We are migrating from Sitecore 8.2 to 9.0.2 and decided to start with "fresh" core, web, and master databases, migrating content on top of blank databases.

What is the most efficient method to transfer role and membership data from 8.2 core database to new 9.0.2 core database? Is it possible to do with on SQL level with tool like Red Gate?

Thank you

Update on 01/11/2019

All answers are helpful! We decided to proceed with option 3 in answer by @jammykam and developed custom code to move data from SQL tables (see list in accepted answer) into the new database. For that we also used solution described here to suspend and enable foreign keys in the target database.

Answer 1

There are a few options to transfer Users and Roles from one instance to another.

1. Sitecore Packages

   You can use the standard Package Designer application from the Development Tools menu in the Sitecore Desktop. You can then add the Roles and Users that you would like to package up for migration, generate the package, download it and then install it on the target instance in the same way you would do for content.

   

2. Serialization

   An alternative is to use the Serialization option from within the User Manager and Role Manager applications. The users and roles will be serialized to (data)/serialization/security folder. You can copy this from the source instance to the target instance and then use the revert option.

   

   Transfer of Passwords

   For both of these options, the password of the user is not transferred, and instead reset. The password is reset to a random value when using Sitecore Packages or to "b" when using serialization.

   You can then either reset the password for the users manually (from the User Manager application), or the users can reset the password themselves by using the Forgot your password option from the login screen, assuming the mail server setting have been configured.

   You also have the option to use the TransferUserPasswords.aspx tool to transfer the passwords from the source and target databases. You can read more about this in the KB article: https://kb.sitecore.net/articles/242631

3. SQL

   The role and user data is stored using ASP.NET Membership provider in SQL Server tables in the Core database. So it is possible to transfer the roles and users with a tool such as Redgate SQL. This has the advantage that it would also transfer the passwords without the need to run the utility above. You will need to ensure you migrate user data from the following tables at a minimum:

   • aspnet_Membership
   • aspnet_Profile
   • aspnet_Roles
   • aspnet_Users
   • aspnet_UsersInRoles
   • RolesInRoles (you can transfer roles using one of the above options instead)

Answer 2

Express migration tool helps to migrate content from old CMS versions (Sitecore 6.6 to Sitecore 8.0 update7) to Sitecore 9.0 rev 171002 (Initial release).

To upgrade from Sitecore 8.2, You have to follow upgrade guideline steps. Please follow below link and take a look at Upgrade Guide and download update packages from Upgrade options section.

https://dev.sitecore.net/Downloads/Sitecore_Experience_Platform/90/Sitecore_Experience_Platform_90_Update2.aspx

Note: If you're planning to upgrade Sitecore CMS, i would suggest to go for Sitecore 9.1 instead of Sitecore 9.0.2