Changing Usernames in Sitecore

Requirements

• Any sitecore version
• Basic SQL skills
• Microsoft SQL Management Studio or equivalent tools

There is no direct way to change a user’s name in Sitecore. Neither the user manager nor the API of Sitecore provides an official way to rename a User. So the official way is to create a new user; but if we create a new user, we also need to assign the same roles the user had before. Using Sitecore Powershell or the API from code behind make this possible; with this, however, the password would need to be changed.

To change the username and keep the password I found only one way I will show you in this blogpost. The task I’m solving in the article is, to change the Usernames of the user to user email address.

The background Microsoft ASP.NET Identity

Sitecore didn’t invent something new when it came up with the user management. Using the standard ASP.NET provides user handling based on domains with roles, profile fields and so on.

You find them all in the core database. For our case the relevant tables are the Membership and the Users table. The Users table contains the username. The email is in the Membership table. Both are linked by the primary key in the UserId column. A simple inner join provides us all information from all users.

select * from [dbo].[aspnet_Users] as U
Inner join [dbo].[aspnet_Membership] as M on U.UserId = M.UserId

 

Preparations

There are two important bits of informations: Whether or the user is approved (Column IsApproved) and whether or not the user is locked out (Column IsLockedOut ). This information can be found in the Membership table. Filtering them is a good idea:

where M.IsApproved = '1'
and M.IsLockedOut = '0'

 

Executing the select on the core database of an out-of-the-box sitecore installation also shows you the default\Anonymous, extranet\Anonymous and sitecore\Admin users. Filtering them and maybe other accounts (e. g. service accounts) is simple: these accounts shouldn’t have an email. So only select users with email address:

M.Email != ''


 

The combined select statement shows us all relevant user accounts:

select * from [dbo].[aspnet_Users] as U
Inner join [dbo].[aspnet_Membership] as M on U.UserId = M.UserId
where M.Email != ''
and M.IsApproved = '1'
and M.IsLockedOut = '0'

 

Duplicate Email address

When we change the username to the email address we must eliminate duplicate email addresses. Of course it is not allowed to give two users the same name. There are two simple ways in which one can approach 1. copy the result to Excel and mark the duplicates; or 2. adjust the select statement to get only the duplicates:

select * from [dbo].[aspnet_Users] as U
Inner join [dbo].[aspnet_Membership] as M on U.UserId = M.UserId
where M.Email != ''
and M.IsApproved = '1'
and M.IsLockedOut = '0'
and (select count(*) from [aspnet_Membership] where Email = M.Email) > 1


 

With this list you can first change the email address of the affected users.

Locked items

If an editor locks items in sitecore for editing, sitecore saves the username in the “Lock” field in the “Workflow” section. This looks like:

<r owner="sitecore\DemoUser" date="20180109T130333Z" />

You will notice that the name of the user is used, not the id. This means, that the user will be unable to unlock items after the change of their username.

Log out

It is highly recommended for all editors to log out and clear all cookies before you change the usernames. Otherwise cookie information might conflict, and the editor can’t log in.

What else

It might be necessary to filter for the domain, too. The domain is part of the username and can be filtered using a string comparison. Single users that don’t need to be changed can also be filtered by their UserId:

and U.UserId != 'EC63AB77-8979-4522-96A9-695396F3C0EE'

 

Last but not least: you should make a backup of your core database!

Change the usernames to the corresponding email

Based on the select statement, we can easily create the update statement.

update U
set U.UserName = 'sitecore\' + M.Email,
U.LoweredUserName = 'sitecore\' + LOWER(M.Email)
from [dbo].aspnet_Users as U
Inner join [dbo].[aspnet_Membership] as M
on U.UserId = M.UserId
where M.Email != ''
and M.IsApproved = '1'
and M.IsLockedOut = '0'


 

Don’t forget to adjust the where clause to your specific case. In general, I always write the select statement first. This way I see exactly which columns I am going to manipulate. Morphing the select statement to an update statement isn’t very complex and you have the good feeling of not breaking anything 😉