Search code examples
asp.netsqlmembership

Merging two ASP.NET membership databases

I have to merge two ASP.NET membership databases with related role and profile tables. Both databases have an identical role and profile structure. They use the built in providers (SqlMembershipProvider and friends). Duplicates are possible.

Do you have recommendations for me? Is there a tool to do this? If not: Can you recommend using the membership API or is it easier to use SQL.

Update

Here is the script I finally used to transfer the membership data. 

insert into targetMembershipDatabase.dbo.aspnet_users
select * from sourceMembershipDatabase.dbo.aspnet_users
where username not in (select username from targetMembershipDatabase.dbo.aspnet_users)

insert into targetMembershipDatabase.dbo.aspnet_membership
select * from sourceMembershipDatabase.dbo.aspnet_membership
where userid in (select userid from targetMembershipDatabase.dbo.aspnet_users)
and not userid in (select userid from targetMembershipDatabase.dbo.aspnet_membership)

insert into targetMembershipDatabase.dbo.aspnet_profile
select * from sourceMembershipDatabase.dbo.aspnet_profile
where userid in (select userid from targetMembershipDatabase.dbo.aspnet_users)
and not userid in (select userid from targetMembershipDatabase.dbo.aspnet_profile)

insert into targetMembershipDatabase.dbo.aspnet_usersinroles
select * from sourceMembershipDatabase.dbo.aspnet_usersinroles
where userid in (select userid from targetMembershipDatabase.dbo.aspnet_users)
and not userid in (select userid from targetMembershipDatabase.dbo.aspnet_usersinroles)

Provided as is. No check for duplicate emails. No warranty that this is working in a more complex scenario.

Solution

  • I not aware of any tool which will do this, but the schema is pretty simple so it would be a straightforward enough job to just do it in SQL. All the keys are GUID anyway, so there shouldn't be a problem.

    Obviously you'd need to check the user_name field for duplicates and email addresses, if the unique email address rule is applied. But if the roles are the same in both databases then all you're really interested in are the users. Once you've got them over it would just be a case of updating the RoleId and ApplicationId in aspnet_Users and aspnet_UsersInRoles.