+1 T-Shirt

+1 T-Shirt !

+1 T-Shirt - JIRA: Justifiably Insane Renamed Asylum

+1 T-Shirt for me.

are you kidding me? we are using a bugtracking system to improve our system and fix bugs faster and the system itself has a major "10Years-Bug"...
where cann I buy these shirts? Fix it please!

Ha - I totally need one of those!

The t-shirt idea is fantastic.

We should have t-shirts made for the 10-year anniversary of this issue and misspell Atlassian on them. "Sorry, there's no way to change the shirts once they're printed. But you can work around it with some masking tape and a marker."

The positive thing is that it's going to be fixed in the next 11 months (probably).

@Max Newbold: The problem is there's no userID, only the username

In other news, I just noticed that this bug about 95 days away from its 10-year anniversary...

Now where's the Like button when I really need it.... Well said, J Fire.

The only reason I can think of why this should be a hard thing is that ages ago, someone at Atlassian made the mistake of using the username string as a foreign key, instead of a user ID that nobody really sees or cares about. Anything user visible or user interactable (like username, email address, name, favorite color, etc.) should be easily changeable.

Dear Atlassian:

Instead of adding new features that most rarely use, do this:

• Change your entity model.
• Provide a database upgrade script. You do not need to maintain schema compatibility. We all backup our databases prior to upgrading anyway.
• Provide a supported script that will change a username for existing releases.

Please. This is what grown-ups do.

I got a request from a customer user last week who got divorced.

I just had a request from a user to change their username because they got married, but I can't... I don't have time to mess around with scripts. We got JIRA to save time not be a maintenance nightmare. Get this fixed Atlassian.

Ouch! About the best you can do is to create a spreadsheet of existing userid and the desired userid. Then create a script to generate the dozen or so SQL statements needed for each userid change. Don't forget the contents of user-based custom fields. Test it all in staging. This won't fix references to old userids in comments, but everything else changes. The hardest bit is gathering all the new userids I find.

I wish I would have known this wasn't possible when we first started using JIRA. We let people sign themselves up, and now have a wide variety of usernames, from email addresses, to full names (including spaces) thanks to the Pivotal Tracker importer tool. Now the workaround is going to be quite a hassle, or we're stuck forever with a bunch of crazy usernames everywhere.

Need this feature for the Google Apps integration.

https://confluence.atlassian.com/display/JIRA/Changing+Usernames+in+JIRA

@Richard Bone: Sorry, but I guess here is not the best place to submit you bug or post this comment. Neither Jira 5.07 nor any other version have got a built-in script to rename a userID.

I'm pretty sure your built-in script comes from the Script Runner plugin (aka Groovy Runner) and the right place to post your comment or submit your bug is here: https://studio.plugins.atlassian.com/browse/GRV (the issue tracker of the Script Runner plugin).

JIRA 5.0.7 has a built-in script runner to rename a userID. Before running the script, users can preview a list of all the changes to be made. After running the script, found that the new user ID was not associated with any groups. See JRA-30596 for details.

At the risk of repetitive begging:

Would anyone be willing to post the change username SQL s/he is using for jira 5.x?

Jamie,
I hope you do not remove this functionality. I've used it successfully and nothing else comes close to being a viable solution.
Robert

Due to the number of responds and the age of the issue, I'm assuming I have no official solution from Atlassian. I'm running into this class of issue far too often with this Jira product. My SQL is rusty and we are running JIRA standalone (no Confluence) with Active Directories and local users as our means of authentication. Plus I have several custom fields with make use of "User Picker". Renaming a username is not some far fetched task that nobody does. In our case, our company was bought by another company and we are transitioning to their AD services.

It is great to see so many variants of the SQL script documented but I would like to see an application note from Atlassian on this subject. I'm already providing functionality via scriptrunner(groovy), velocity templates and perl scripts, now I have to dust off my sql – I'm NOT IMPRESSED.

Hi Jamie,

what do you plan to remove? The renaming user function? I hope this is a joke, it is so important for us. Please fix the problem with other directories (where DirectoryID is not 1) and maybe also across different directories, but that is not important for me.

Please go on.

Hi Jamie,

important for me to state two things:

• we tested it of course in a development environment - I mean, hey I am working in QA
• when I stated "I have lost my faith in ScriptRunner." I meant of course only related to the renaming of users. We're using the script runner successfully for a few years right now and it saves us plenty of time and work. Great work, go on!

Can't be bothered to look right now but I'm sure that somewhere I've stated that renaming users across directories is not supported. I've certainly never tested that.

Anyway I'll remove this from the next release. Too few people take note of the advice to try on a copy of the production instance. I don't understand why anyone would do such a large operation without testing first but that's the way it seems to be.

@Kai: we use a kerberized Apache as proxy to log into Jira. From Jira's point of view this is just a simple login without Crowd or direct LDAP connection.
I did some tests with the script runner several months ago with mixed results: sometimes it worked, sometimes I got random (Java-) error messages (unfortunately I cannot provide you these anymore). When it worked, the result seemed to be okay & consistent.
However, I do not trust this solution as well because of the random errors.

I also think this must be fixed within Jira itself. Compensating for a low-level design issue by a high-level workaround is in my opinion not acceptable and can never be reliable.

Question to all commenters, who stated that it works fine with the groovy-script-runner: What kind of authentication-setup do you use? Most likely you do not use LDAP->CROWD->JIRA , or am I wrong?

When I (with setup LDAP->CROWD->JIRA) used the script "Renames a user ID" the script completes the job successful, but having a look at the recently changed user reveals folowing:

• field "Username" (viewed in the admin-view) remains still unchanged and furthermore:
  • a JQL-query for "reporter = new.name" shows zero issues ("No matching issues found.")
  • a JQL-query for "reporter = old.name" shows all issues, but additional a warning message ("The value 'old.name' does not exist for the field 'reporter'.")

why I tend to second the comment from Owen Funkhouser above:

I have lost my faith in ScriptRunner.

at least when it comes up to renaming users.

Also using "groovy-script-runner". Working great for use, have probably renamed about 25 user. It also allows you to merge user account which let us clean up all the account we could not rename in the past (we created new accounts instead since we previously could not rename).

Ohh my god is this task old!

But all jira-user (from 3.1.13 and newer) can use the thrid-party-extension named "groovy-script-runner". This plugin is working fine, and don't make any trouble. We are use it since jira 5.0 and has renamed approx 50 users at this time.

Would anyone be willing to post the change username SQL s/he is using for jira 5.x?

Agreed, this is frustrating as it seems such a simple request.

I just used the ScriptRunner to merge a user from an old LDAP directory to the new username. It completely corrupted my JIRA v5.0 databse. I am glad I did a backup to XML first.

Can someone post what SQL tables I need to modify change the Assignee and Reporter fields to the new user? I have lost my faith in ScriptRunner.

Note that Crowd does not solve the problem associated with renaming a username.

See https://confluence.atlassian.com/display/CROWD/Adding+a+User: "Username — The user's login name. Within a given directory, the username must be unique. Note that you cannot change the username once the user has been created."

Eric R. Fairhurst is my new running mate.

I think they want to promote Crowd and obviously view this as a dead end and won't fix. I gave up on them fixing this years ago.

Drew Peifer 2012!

Absorb these stats for a second...

Created: 08/Apr/03 7:57 AM

Votes: 639

Watchers: 326

• Agreed, why would userid be a foreign key. It's preposterous. at least passwords aren't stored in plain text though.
• Agreed, in lieu of updating JIRA, a set of official SQL scripts or at least a lits of the offending user tables would be handy, and a show of good faith on Atlassian's side. In fact, the majority of the work for either hotfix solution probably exists within this comment thread
• Agreed, we have all wasted a lot of time and money making JIRA do things that JIRA should already be able to do on its own. Some people have even made money doing this, and they are plugin developers.

Thank you all commenters, for bumping this issue since 2003.

Thank you Atlassian for allowing some plugin developers to make a little money off of this bug.

Seriously though. You should fix it. It's getting annoying.

Even a hacky fix would do for now, like keeping the userid as a back-end foreign key but duplicating that value as an editable attribute (displayname? newname? get-users-off-our-backs-ID? who cares), then changing visual templates so you're displaying that new attribute where userid is currently being displayed (if we can do a find and replace, why can't you guys? keep your DB schema, just change the visual templates so we're looking at AND EDITING a different attribute of the user object).

We don't care how messy your code is, we care how messy our JIRA instances are, and they're getting messy. Since you guys are closed-source, it shouldn't matter what black magic you do on the back-end. Just give the users what they want and close this bug. It's killin me, smalls.

When will this issue be fixed?

facepalm - this issue can't be open since 2003. Even Microsoft can handle username changes meanwhile.

As usual basic functionality has to be implemented by customers. For PostgreSQL here is a procedure that does the account renaming:
https://gist.github.com/2352960 (patches and beers are welcome).

Hi Atlassian,

Could you please confirm this feature is include in JIRA 5.1 ?

Regards,
Sven.

Thanks Chad, I will definitely check it out. Looks like I might be able to use some of the workflow enhancements as well.

If you install the script runner plugin for JIRA, it provides an inbuilt script to rename users. I've been using it for about a year, and it works really well.

To Atlassian

The fact that one of your customers was so fed up with your poor workmanship that they built a kludge and shared it with all of us does not free you from the obligation of actually fixing your software.

FWIW, I recently undertook a username renaming exercise, choosing the route of backing up to XML, editing the XML, then restoring from the edited XML.

To do the renaming I created a perl script that used regexps on all known expressions where usernames were found to be used as keys. This was to avoid the problem of collateral renaming damage. The list of keywords was derived empirically by analyzing everywhere that a username was found. WARNING: The list is probably not exhaustive, and seems to be based on the extent of activities and workflows you have in your JIRA.

#    name="fred"
#    userName="fred"
#    username="fred"
#    lowerUserName="fred"
#    assignee="fred"
#    author="fred"
#    caller="fred"
#    childName="fred"
#    lowerChildName="fred"
#    entityId="fred"
#    lead="fred"
#    newvalue="fred"
#    oldvalue="fred"
#    reporter="fred"
#    roletypeparameter="fred"
#    updateauthor="fred"
#    
#    <meta name="jira.update.author.name">fred</meta>

A big caveat you should be aware of concerns the <ExternalEntity> entries. Unknowingly, I had external entity entries with the same name as usernames I was renaming to. This resulted in duplicate (non-unique) external entity names after the perl script had run. Eg:

<ExternalEntity id="10" name="fred" type="com.atlassian.jira.user.OfbizExternalEntityStore"/>
[...]
<ExternalEntity id="515" name="fred" type="com.atlassian.jira.user.OfbizExternalEntityStore"/>

This caused JIRA to crash. You must ensure that your renaming does not result in duplicate names in the ExternalEntity and User entry lists.

Apart from that, the script worked fine for my installation, but there are no guarantees. USE AT YOUR OWN RISK. Keep a copy of your original unedited backup/export XML and if things go pear-shaped just restore from that.

#!/usr/bin/perl -i~
#
# Edits file in-place changing all known JIRA keys that contain
# usernames from oldvalue to newvalue. Run it against an XML backup file "entities.xml".
# Original file is preserved as "entities.xml~".
#
# Example: % jira-rename-user.pl fred:fblogs nick:ngianniot entities.xml
#
# BE CAREFUL: DO NOT CREATE ANY DUPLICATES IN THE <ExternalEntity> section!!!!!

use warnings;

$USAGE = "usage: $0 from-username:to-username [...] file\n";

while (@ARGV > 1)
{
    $from_to = shift;
    (($from, $to) = split(/:/, $from_to)) == 2 or die "bad from:to arg [$from_to]\n";
    push(@pairs, [$from, lc($to)]);
}

@pairs > 0 or die $USAGE;

foreach $pair (@pairs)
{
    my ($from, $to) = @{$pair};
    print "from=[$from] to=[$to]\n";
}

print "file=[@ARGV]\n";

@keys = qw(
name
userName
username
lowerUserName
assignee
author
caller
childName
lowerChildName
entityId
lead
newvalue
oldvalue
reporter
roletypeparameter
updateauthor
);

while (<>)
{
    foreach $pair (@pairs)
    {
	my ($from, $to) = @{$pair};

	foreach $k (@keys)
	{
	    s|\b$k="$from"|$k="$to"|g;
	}
	
	s|<meta name="jira.update.author.name">$from</meta>|<meta name="jira.update.author.name">$to</meta>|g;
    }	
    print;
}

We have utilized the SQL scripts across multiple clients several times. You need to be proficient in SQL and have experience working with JIRA. However, if you have at minimum an adequate database person and a solid development environment then there is nothing to fear with this.

I think that it is important to remember: If you have any custom fields or other customisations, then the plugins, scripts and SQL snippets are not going to fix all your problems and you are very likely to sit with a system that needs to be restored from backup. This is not a task that presently can be attempted with a "hope it works" attitude.

Where could we find a list of tables in MSSQL that need updating in order to rename/merge user accounts in Jira 4.4.3?

Don't worry, Atlassian, you don't have to come up with a kludge to fix this because someone else already did!

Everyone, check out the script runner plugin. It has inbuilt scripts, and one of my favorite is "Rename user", which also lets you merge users!

(I love this ecosystem that sprang up around fixing Atlassian's inadequacies. It's like a construction project that frequently leaves holes where windows should be and occasionally just throws up a fireman's pole in lieu of stairs. But you can build steps yourself! So who cares?)

+1 on the above comment. We recently tried to merge users, and it was not a pretty sight. Atlassian, the hole is getting deeper....

here is the script I used for MSSQL db. One thing to note is to make sure that the new name does not already exist in jiraschema.external_entities. We use LDAP and it had already pulled in the new user name and cause errors since the rename causes an duplicate record. Also jiraschema.userhistoryitem caused some issue, not sure how it got resolved... i messed with it a bit and not sure how that got renamed

update jiraschema.changegroup set AUTHOR='newusername' where CAST(AUTHOR AS nvarchar(max))='oldusername'
update jiraschema.changeitem set OLDVALUE='newusername' where CAST(OLDVALUE AS nvarchar(max))='oldusername'
update jiraschema.changeitem set NEWVALUE='newusername' where CAST(NEWVALUE AS nvarchar(max))='oldusername'
update jiraschema.columnlayout set username='newusername' where CAST(username AS nvarchar(max))='oldusername'
update jiraschema.component set LEAD='newusername' where CAST(LEAD AS nvarchar(max))='oldusername'
update jiraschema.external_entities set NAME='newusername' where CAST(NAME AS nvarchar(max))='oldusername'
update jiraschema.favouriteassociations set USERNAME='newusername' where CAST(USERNAME AS nvarchar(max))='oldusername'
update jiraschema.fileattachment set author='newusername' where CAST(author AS nvarchar(max))='oldusername'
update jiraschema.filtersubscription set username='newusername' where CAST(username AS nvarchar(max))='oldusername'
update jiraschema.jiraaction set AUTHOR='newusername' where CAST(AUTHOR AS nvarchar(max))='oldusername'
update jiraschema.jiraaction set UPDATEAUTHOR='newusername' where CAST(UPDATEAUTHOR AS nvarchar(max))='oldusername'
update jiraschema.jiraissue set reporter='newusername' where CAST(reporter AS nvarchar(max))='oldusername'
update jiraschema.jiraissue set assignee='newusername' where CAST(assignee AS nvarchar(max))='oldusername'
update jiraschema.jiraworkflows set creatorname='newusername' where CAST(creatorname AS nvarchar(max))='oldusername'
update jiraschema.membershipbase set USER_NAME='newusername' where CAST(USER_NAME AS nvarchar(max))='oldusername'
update jiraschema.OS_CURRENTSTEP set owner='newusername' where CAST(owner AS nvarchar(max))='oldusername'
update jiraschema.OS_CURRENTSTEP set caller='newusername' where CAST(caller AS nvarchar(max))='oldusername'
update jiraschema.OS_HISTORYSTEP set owner='newusername' where CAST(owner AS nvarchar(max))='oldusername'
update jiraschema.OS_HISTORYSTEP set caller='newusername' where CAST(caller AS nvarchar(max))='oldusername'
update jiraschema.portalpage set  username='newusername' where CAST(username AS nvarchar(max))='oldusername'
update jiraschema.project set lead='newusername' where CAST(lead AS nvarchar(max))='oldusername'
update jiraschema.projectroleactor set roletypeparameter='newusername' where CAST(roletypeparameter AS nvarchar(max))='oldusername' and roletype='atlassian-user-role-actor';
update jiraschema.schemepermissions set perm_parameter='newusername' where CAST(perm_parameter AS nvarchar(max))='oldusername' and perm_type='user';
update jiraschema.searchrequest set authorname='newusername' where CAST(authorname AS nvarchar(max))='oldusername'
update jiraschema.searchrequest set username='newusername' where CAST(username AS nvarchar(max))='oldusername'
update jiraschema.trustedapp set CREATED_BY='newusername' where CAST(CREATED_BY AS nvarchar(max))='oldusername'
update jiraschema.trustedapp set UPDATED_BY='newusername' where CAST(UPDATED_BY AS nvarchar(max))='oldusername'
update jiraschema.userassociation set SOURCE_NAME='newusername' where CAST(SOURCE_NAME AS nvarchar(max))='oldusername'
update jiraschema.userbase set username='newusername' where CAST(username AS nvarchar(max))='oldusername'
update jiraschema.worklog set author='newusername' where CAST(author AS nvarchar(max))='oldusername'
update jiraschema.worklog set updateauthor='newusername' where CAST(updateauthor AS nvarchar(max))='oldusername'
update jiraschema.customfieldvalue set stringvalue='newusername' where CAST(stringvalue AS nvarchar(max))='oldusername'
update jiraschema.userhistoryitem set username='newusername' where CAST(username AS nvarchar(max))='oldusername'

cwd_user was introduced by embedded crowd for user management in Jira 4.3, and isn't in previous installations. AFAIK the problem still exists, cwd_user is just yet another table that has to be updated when you rename a user. I suspect the introduction of crowd into Jira is the first step in properly fixing this problem.

cwd_user is a Crowd table? It's not in my schema.

I'd suggest the "center" of the entire problem is the decision a long time ago to use the username column of the userbase table as a foreign key everywhere that a link to the 'user' is required, combined with the fact that people want to change their usernames.

In none of the work-around scripts presented do I see see mention of cwd_user which, to my knowledge, is at the center of this entire problem. Am I missing something?

So - more than a year has passed since the last statement from Atlassian (Edwin Wong on 14/Mar/10). I just raised the question on the plans to fix this in the related issue for Confluence (CONF-4063) and we got a really annoying message from Atlassian, basically saying that they will not fix this issue in the near future. Both issues together have been voted for by almost 800 users from which I suspect most are paying their yearly maintenance fee to Atlassian. So Atlassian - 800 customers voting and an unknown number just turning away from you and you are not willing to address this issue NOW? Disappointing ...

Some other things to watch out for when using the scripts here:

• GreenHopper preference settings in propertytext.propertyvalue
• jiraworkflows where the username is embedded in the workflow XML

Hi... BTW if anyone has questions or feedback on the plugin probably best to add as a comment here or a bug here.

Matt, I have tested it like that yes, but as I think you said earlier the challenge is to ensure that everywhere a user ID could be is actually exercised.

Frank, the preview shows the number of records of each type updated, rather than the actual values. In the case of the filters though it's more complicated than a simple find and replace in the db, which is one of the advantages of this method.

It's possible of course that something has been missed... I don't use jira in every possible configuration. However if that's the case I can't see that you'd be worse off than having done it through sql, or (worst possible idea), a find and replace in the dumped XML.

cheers, jamie

Jamie, sounds promissing. Just a question, i couldnt find some tables in your picture (e.g. trustedapp, external_entities, etc.) on which script is your code made? (sorry, i currently have only a 3.13 testinstance otherwise i maybe would try it practically ) --Frank (do you think it would be a improvement if you show all updated fields? so we could compare it to SQL Scripts mentioned here)

Jamie,

I'm impressed! Do you test it with a unique userid string, then grep the backup up after the change to make sure you got all instances. The list of tables is tricky to maintain except by inspection of the database schema in two JIRA versions.

~Matt

Renaming a user is now part of the Script Runner plugin (details, installation instructions).

This will make all necessary changes in the database and reindex only those issues that require it. It will also modify any filters that have assignee/reporter/single or multi user custom field parameters that reference the user to be renamed. You do not need to restart or reindex.

This is for 4.2 at the moment only, I'll add support for previous versions if there is demand and no major issues are found.

@Thomas: i dont think a 3.8 script is good for 3.13. I guess for Oracle your best try is to use Srinis Script mentioned here: http://jira.atlassian.com/browse/JRA-1549?focusedCommentId=217005&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-217005

We've moved to Crowd user management through AD usernames. Looking through the XML backup (because I'm no DBA) there's ExternalEntity with IDs. The problem I'm trying to fix is one of mixed case usernames and a lower case usernames (for the same user - e.g. ABrown and abrown).

I'm confident the uppercase versions have been changed to lower case in the XML, the ExtenalEntity lines that would have created the mixed case entries removed, and the row counts have been changed. I still can't import the XML though.

Can anyone think what I may have missed modifying?

I also notice that this is now in the top 5 voted issues.

Does anyone know if Peter's (http://jira.atlassian.com/browse/JRA-1549?focusedCommentId=79599&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-79599) solution works for version 3.13?

Nitish, thanks, I was able to use that statement in a function which uses usermigration to update them all automatically, as the other statements do. I have added those statements to my comment above.

Hi erin,
Adding the below script could further enhance it

Update searchrequest Set REQCONTENT = Replace (REQCONTENT, 'OLDVAL' , 'NEWVAL');

because sometimes there are filters stored in such a fashion as
project=JRA and assignee= OLDVAL

Regards,
Nitish

Here is the PostGreSQL script for JIRA 4:

update changegroup set AUTHOR=newusername from usermigration where AUTHOR=oldusername;
update changeitem set OLDVALUE=newusername from usermigration where OLDVALUE=oldusername;
update changeitem set NEWVALUE=newusername from usermigration where NEWVALUE=oldusername;
update columnlayout set username=newusername from usermigration where username=oldusername;
update component set LEAD=newusername from usermigration where LEAD=oldusername;
update external_entities set NAME=newusername from usermigration where NAME=oldusername;
update favouriteassociations set USERNAME=newusername from usermigration where USERNAME=oldusername;
update fileattachment set author=newusername from usermigration where author=oldusername;
update filtersubscription set username=newusername from usermigration where username=oldusername;
update jiraaction set AUTHOR=newusername from usermigration where AUTHOR=oldusername;
update jiraaction set UPDATEAUTHOR=newusername from usermigration where UPDATEAUTHOR=oldusername;
update jiraissue set reporter=newusername from usermigration where reporter=oldusername;
update jiraissue set assignee=newusername from usermigration where assignee=oldusername;
update jiraworkflows set creatorname=newusername from usermigration where creatorname=oldusername;
update membershipbase set USER_NAME=newusername from usermigration where USER_NAME=oldusername;
update OS_CURRENTSTEP set owner=newusername from usermigration where owner=oldusername;
update OS_CURRENTSTEP set caller=newusername from usermigration where caller=oldusername;
update OS_HISTORYSTEP set owner=newusername from usermigration where owner=oldusername;
update OS_HISTORYSTEP set caller=newusername from usermigration where caller=oldusername;
update portalpage set  username=newusername from usermigration where username=oldusername;
update project set lead=newusername from usermigration where lead=oldusername;
update projectroleactor set roletypeparameter=newusername from usermigration where roletypeparameter=oldusername and roletype='atlassian-user-role-actor';
update schemepermissions set perm_parameter=newusername from usermigration where perm_parameter=oldusername and perm_type='user';
update searchrequest set authorname=newusername from usermigration where authorname=oldusername;
update searchrequest set username=newusername from usermigration where username=oldusername;
update trustedapp set CREATED_BY=newusername from usermigration where CREATED_BY=oldusername;
update trustedapp set UPDATED_BY=newusername from usermigration where UPDATED_BY=oldusername;
update userassociation set SOURCE_NAME=newusername from usermigration where SOURCE_NAME=oldusername;
update userbase set username=newusername from usermigration where username=oldusername;
update worklog set author=newusername from usermigration where author=oldusername;
update worklog set updateauthor=newusername from usermigration where updateauthor=oldusername;
update customfieldvalue set stringvalue=newusername from usermigration where stringvalue=oldusername;
update userhistoryitem set username=newusername from usermigration where username=oldusername;
-- The following function employs string replacement to update filters and may be damaging if any of the old usernames are also words that exist in the filter definition as a non-username string. Uncomment to use this at your own risk.
-- create language plpgsql;
-- create or replace function updatejirafilters() returns char as $$
-- declare
-- line record;
-- begin
-- for line in select * from usermigration loop
-- update searchrequest set REQCONTENT=replace(REQCONTENT, line.oldusername , line.newusername);
-- end loop;
-- return 'done';
-- end;
-- $$ language plpgsql;
-- select updatejirafilters();

username in LDAP is changeable, usually thats the uid. The username is in many companies connected to the name of a user (like mail-address or abbreviations of a users fullname). If that user's name is changed usually the uid will be changed accordingly (at least in our company thats done like that).
You can ship around that obstacle by using a unique identifier which consists of unconnected elements (like a random number/character combination). But its not readable anymore (because l75gd is much less understandable as fst (e.g. in my case)).

The Design Flaw is that you cant change the username while the system is running and/or without having a automated possibility for it (like a trigger in the AD/LDAP which syncs the JIRA instance). I guess if the API would have a ChangeUserName method i wouldnt care if there is that kind of design flaw (of course there must not be a downtime for reindexing, in our jira that would be around 30 to 45min.)

I dont say confluence doesnt have sideeffects on renaming without reindexing, but at least it can still operate

p.s. i have seen usernames which say old account/dont use me i guess thats the thing that wants to be avoided

Hello Tom,

you got it.
That is the whole point of this issue, which is open since such long time.

Atlassian uses as userid the same username which is input by the users at login and needed to do user authentication over LDAP.
Instead, they should use a hidden number / id, as it is in every good database design.
But they don't want to and prefer to stick year after year to their original mistake!

Bettina

Bettina,

Is in LDAP the username changeable ?
In that case there must be an underlying identifier that remains the same.

Hello Tom,

the problem is that in case of external user management - a widely used Jira feature - the username MUST be identical with the username that comes over LDAP.
In my example, I have to keep the Jira username up-to-date with our Active Directory.
Every time a user is renamed in the Active Directory, I also have to change the username in Jira.
You can replace "username" with "userid", the problem stays the same, even if nobody else than me (as Jira Admin) were able to see the username / userid.

So the username / userid is NOT meaningless, since it is exchanged with other systems!

Cheers
Bettina

I don't see why this is such a problem for Atlassian to follow up on.
Basic rule is simple.
Id's should never be visible to users, and be meaningless.
The username becomes userid, and the full name belonging to that user-id gets shown everywhere.
This way the user-id never needs to be changed, only 1 change is needed when the release with this change is done.

I'm afraid that this improvement will never be implemented, but nevertheless I vote for it since it's a MUST HAVE in my opinion.

As a new JIRA customer, I must say... This is an epic/ridiculous fail for Atlassian.

We are in the same situation, we need to merge two JIRA instances where there are cases of a single user having two different userids on those instances. So we need to rename those users on one of the instances, but we can't.

And this is flying around, opened and voted for, since 2003. LOL.

1. A solution that depends on a production restart and reindex is not a practical solution to this problem
2. The fact that my username seems to be the primary key, littered about throughout the database as the foreign key on however many tables, seems like really smelly development to me.

I used a simpler form

Update searchrequest Set REQCONTENT = Replace (REQCONTENT, 'OLDVAL' , 'NEWVAL');

It worked well!

I'm not going to pretend to be skilled at SQL scripts, but the following seems to work for replacing values in the reqcontent column:

update JIRASchema.searchrequest set reqcontent=replace(Cast(reqcontent AS NVARCHAR(Max)),'oldvalue','newvalue');

I got the idea from here - http://stevenharman.net/blog/archive/2007/12/30/a-better-search-and-replace-for-your-database.aspx

Hi
The script

update xxxx set xxx=yyyy where xxx=zzz

it is working fine.
Just that 1 point is missing.

The search request column has a table "request".
It has filtered stored in the form of SQL search.
eg:

SearchRequest id="10223" name="All Cranial" author="prossj" user="prossj" project="10011" request="reporter = alexp ORDER BY key DESC" favCount="1"/>

I find that the scripts will not correct that "alexp".
Please look into it!

The last mentioned db schema changes have been in 4.0 and 3.12. As 4.0 were merely db type migrations to larger data types i guess you should be save with the 3.13 scripts mentioned here.

Has anyone tried this on 4.2.0 (or, I guess, 4.2.1) yet? Particularly with MySQL? We have some users to rename.

Rain, i guess Srinis Oracle Script is the latest posted here. OracleDB-UserNameUpdate-SQL-Jira3.13.4-Enterprise.txt. From 3.13 to 4.1 there werent any username relevant tablechanges as far as i know. Whoever uses the latest version is welcome to be the pioneer to try it

Our LDAP directory has capital letters in the username (eg. AustinS). If i create similar users in JIRA in lowercase (austins)(JIRA doesnt support uppercase), will the LDAP integration be possible directly.(USING THE DATABSE EDITING WORKAROUND)

Anyone know if Peter's script works with 4.2?

Please add this. Our IT person just asked me for help with the typical case (user gets married). She created a new JIRA user with the same groups and was going to just try to reassign and change reporter on all the tickets the old username had (700+). Of course, due to restrictions on editing closed issues, plus various permission schemes we've set up, even I am not able to bulk change the issues. I now have to start combing through our various permissions and project setups just so that I can get bulk change working. I'm sure I'll get it going eventually but it's a hokey workaround.

Thanks,

We are running JIRA for our company using LDAP as well, and every few months it seems we have a request to change user names due to marital status. This feature is very much needed and would save us from so many headaches and hours of explanation to users.

I have successfully (I hope!) renamed a user using Frank Stiller's SQL scripts. Thank you, Frank! I added one table to the script: userhistoryitem, discovered when I grepped the .xml backup for the oldusername after running Frank's script.

update userhistoryitem x inner join usermigration u on x.username = u.oldusername set x.username = u.newusername;

Atlassian, you should officially sanction and support Frank's script at the very least.

Please hurry!

Nancy, I am delighted you are able to keep your sense of humor. :-S

Well, we have been live with Jira for one month and have received our first user rename request. I approach this with serious trepidation and much frustration. This user cannot log in at all now because our IT department has already renamed her username. Atlassian, you have really dropped the ball on this one. I am really embarrassed to explain this to my user community! Please hurry!

I think the best approach to solving this is a plugin that does what all the different versions of the scripts out there do.

1. It can also check the version of the database schema to know what to change
2. It can handle the more complex cases where the username is embedded in a parameter to a scheme, e.g. notification scheme with a Custom User Field parameter
3. It can handle renaming a user and also merging two userids into one.
4. It can check for existing users with the same name

Testing it is likely to be the hardest thing. How do I set up a JIRA instance with a particular userid embedded everywhere it can be?

~Matt

p.s. If I had a DolLorean DMC-12 and the rest, I've got a few other things to fix first before JRA-1549.

I will stress my previous comment once again...

I could care less how this information is stored in the database. All I want is a way to rename a user. It doesn't happen every day, so some supported function in the administration section where I can enter/pick a user and enter their new name which does whatever it needs to do in order to make everything work (update all the username fields like the scripts attached to this post do) would be a perfectly acceptable answer in my opinion. Even just a supported script to download and run that I don't have to dig through an issue to find would be fine. Much better than the string replacement in XML which could replace much more than you really want it to.

Great work Matt Doar,

Now we only need get the hold of George McFly, Dr. Emmett "Doc" Brown, a slightly modified DeLorean DMC-12 and some plutonium to feed the flux capacitor and this thing will be sorted in no time..

Regards

Jonas

I found this on a Confluence documentation page, and it goes some way to explaining the historical decision not to use the userbase id field in JIRA.

"Since it is not guaranteed that users will be stored in the confluence database (eg. using external user management), the username instead of an id is used as the foreign key."

Vlastimil,

I've pondered doing this as a plugin too. Let us know how you get on.

~Matt

Now that you've mentioned you are building a plugin, Atlassian is going to stop working on any supported solution - it has happend before with cluster support. However if you can make it work in less than 7 years you'll be ahead of the curve and will make at least 443 people very happy. Thanks for stepping up to the plate.

P.S. As frustrating as some of these road blocks are I still love what Jira can do.

Hi all,

I'm just working on plugin which allows user rename and user merge in jira 4.1.2 directly from admin console. Work is very promising for now.

I've only just read the August update which states that we're going to be waiting another 6 months in all likelihood. Is there any way of codifying the recommended workarounds so that Jira would apply the database changes itself? I really think that, after 7+ years, we could do with a tactical solution rather than waiting for the mythical Crowd integration to happen. What do you reckon Atlassian? Cheers, Mike

good news for me, personally, is that my senior managers haven't complained when I suggested we could save money by not renewing our jira, confluence and greenhopper licences for a while, which expired yesterday, either until there's a compelling new feature we can't live without which includes the authentication model i.e. the ability to rename users.

We would love to have a supported solution for this use case at least for mass mutations such as renaming n user accounts when we link them to LDAP servers. Our current solution which we applied once when migrating our own user database to LDAP authentication and which we will apply again migrating one of our customer which we cater ActiveDirectory services to is dumping to XML JIRA, transforming and reloading. This takes days of quality assurance until we are 100% sure that the result is fully consistent again.

I didn't want to create any different tables, so I just changed them all to a format like

UPDATE [table] SET [column] = [newvalue] WHERE [column] = [oldvalue];

I didn't get any MySQL errors. The only error I did get was an application error when trying to look at the user details page. Since my user had already been in the application using their new username, the external_entities table already had an entry with that new username so when I updated the old one it made a duplicate entry which the application apparently doesn't like, so I had to remove that. So if your user has already been using the application with their new username, I wouldn't bother with the external_entities update, it hasn't seemed to cause any issues for me having both the old and new.

When you say basically, what modifications did you make?

Also, were any MySQL errors observed or Tomcat application errors?

I basically ended up using the one from http://jira.atlassian.com/browse/JRA-1549?focusedCommentId=170640&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-170640