Steve's Ramblings

Renaming JIRA Users

So you use JIRA do you? Nice, so do I.

Ever have someone get married? divorced? a typo in their user name? Every try and change it? Yeah, not so easy.

Atlassian has not set a release where there will be a fix, so we humble JIRA administrators have to make due with what we can. At this current job we are using version 4.0 (with plans to upgrade this summer). We also used email addresses as the user name rather than the AD based user name. If you want to use AD integration, it is best to match user names. So we had a pressing need to rename everyone. What is the right way? Atlassian tells us to export, search and replace, then import. Yep. Kludgey. Plus my import was not working for some reason. What else can we do? Database Updates! Yeah!

I found my first SQL example in the 7 year old rename user bug. It was published by Jeff Turner. It mostly works on the 4.0 scheme. Some updates are needed.

Here is the updated SQL for a 4.0 schema:

update jiraissue set reporter='newuser' where reporter='olduser@example.com'; update jiraissue set assignee='newuser' where assignee='olduser@example.com'; update jiraaction set AUTHOR='newuser' where AUTHOR='olduser@example.com'; update changegroup set AUTHOR='newuser' where AUTHOR='olduser@example.com'; update changeitem set OLDVALUE='newuser' where OLDVALUE='olduser@example.com' and FIELD='assignee'; update changeitem set NEWVALUE='newuser' where NEWVALUE='olduser@example.com' and FIELD='assignee'; update searchrequest set authorname='newuser' where authorname='olduser@example.com'; update searchrequest set username='newuser' where username='olduser@example.com'; update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user"; update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user"; update searchrequest set authorname='newuser' where authorname='olduser@example.com'; update membershipbase set USER_NAME='newuser' where USER_NAME='olduser@example.com'; update OS_CURRENTSTEP set owner='newuser' where owner='olduser@example.com'; update OS_CURRENTSTEP set caller='newuser' where caller='olduser@example.com'; update OS_HISTORYSTEP set owner='newuser' where owner='olduser@example.com'; update OS_HISTORYSTEP set caller='newuser' where caller='olduser@example.com'; update fileattachment set author='newuser' where author='olduser@example.com'; update filtersubscription set username='newuser' where username='olduser@example.com'; update project set lead='newuser' where lead='olduser@example.com'; update userbase set username='newuser' where username='olduser@example.com'; update customfieldvalue set stringvalue='newuser' where stringvalue='olduser@example.com'; update columnlayout set username='newuser' where username='olduser@example.com'; update portalpage set username='newuser' where username='olduser@example.com'; update userhistoryitem set USERNAME='newuser' where USERNAME='olduser@example.com'; update worklog set AUTHOR='newuser' where AUTHOR='olduser@example.com'; update worklog set UPDATEAUTHOR='newuser' where UPDATEAUTHOR='olduser@example.com'; update notification set notif_parameter='newuser' where notif_parameter='olduser@example.com'; update component set lead='newuser' where lead='olduser@example.com'; update columnlayout set username='newuser' where username='olduser@example.com';

Once you update your DB with all of those update statements you need to refresh your JIRA indexes. This is done via the JIRA Administrator->Indexes page.

I have done this for a couple of users today and everything looks OK so far. I will be doing it for bunches of users in the next couple of days and will update this post if needed.