Archive

Archive for June, 2014

Migrate Users Across Sitecore Databases

This script is used to migrate users in a specific role from two different Sitecore Instances.

As you can imagine, it runs on the Core databases.

begin tran
declare @roleId  uniqueidentifier
declare @destinationRoleId  uniqueidentifierselect TOP 1 @roleId  =RoleId from dbo.aspnet_Roles where LoweredRoleName=’sitecore\sourcegroup’

select @roleId

select TOP 1 @destinationRoleId =RoleId from [OtherDB].dbo.aspnet_Roles where LoweredRoleName=’sitecore\destinationgroup’
select @destinationRoleId
select *from aspnet_UsersInRoles where RoleId=  @roleId

select *from aspnet_Roles where RoleId=  @roleId

select *from [OtherDB].dbo.aspnet_Roles where RoleId=  @destinationRoleId

select distinct sourceUsers.ApplicationId, sourceUsers.UserId,sourceUsers.UserName,sourceUsers.LoweredUserName,sourceUsers.MobileAlias,sourceUsers.IsAnonymous,sourceUsers.LastActivityDate
into #TmpUsers
from dbo.aspnet_Users as sourceUsers
inner join dbo.aspnet_UsersInRoles on sourceUsers.UserId = aspnet_UsersInRoles.UserId
where aspnet_UsersInRoles.RoleId=@roleId
and sourceUsers.LoweredUserName not in (select distinct LoweredUserName from  [OtherDB].dbo.aspnet_Users )

select * from #TmpUsers

INSERT into [OtherDB].dbo.aspnet_Users
select * from #TmpUsers

INSERT into [OtherDB].dbo.aspnet_UsersInRoles
select UserId, @destinationRoleId as RoleId from #TmpUsers

select distinct gcu.ApplicationId, gcu.UserId,gcu.UserName,gcu.LoweredUserName,gcu.MobileAlias,gcu.IsAnonymous,gcu.LastActivityDate
into #TmpExistingUsers
from dbo.aspnet_Users as sourceUsers inner join dbo.aspnet_UsersInRoles on sourceUsers.UserId = aspnet_UsersInRoles.UserId
inner join [OtherDB].dbo.aspnet_Users gcu on gcu.LoweredUserName = sourceUsers.LoweredUserName
where aspnet_UsersInRoles.RoleId=@roleId

select * from #TmpExistingUsers

INSERT into [OtherDB].dbo.aspnet_UsersInRoles
select ExistingUsers.UserId, @destinationRoleId as RoleId from #TmpExistingUsers ExistingUsers
left outer join [OtherDB].dbo.aspnet_UsersInRoles   on ExistingUsers.UserId = [OtherDB].dbo.aspnet_UsersInRoles.UserId and [OtherDB].dbo.aspnet_UsersInRoles.RoleId = @destinationRoleId
where [OtherDB].dbo.aspnet_UsersInRoles.UserId is null

drop table #TmpUsers

drop table #TmpExistingUsers

select *from [OtherDB].dbo.aspnet_UsersInRoles where RoleId=  @destinationRoleId

–rollback tran
commit tran

Advertisements
Categories: DEV Tags: ,