Wrong user_name reference when renaming username

[Stefan Feldmann]

Hello,

I have the following problem when renaming a username for an existing user.

I have created a new user, for instance username 'torben.ulrich@xyz.de' and email also 'torben.ulrich@xyz.de'.

After that I created issues where this user torben.ulrich@xyz.de is the ASSIGNEE.

Then I renamed the username from 'torben.ulrich@xyz.de' to tUlrich.

The table in SQL Server database dbo.cwd_user is showing the correct value in the field user_name: tUlrich

The problem now is, that the user is renamed correctly, but new and existing issue still have the first username 'torben.ulrich@xyz.de' in a user field, for instance ASSIGNEE in the table jiraissue.

The table dbo.jiraissue still has the wrong value in the field ASSIGNEE: torben.ulrich@xyz.de.

My question now is, how can I correct this field (reference) in dbo.jiraissue?

Regards,

Stefan

[Andy Heinzer]

You are not expected to be able to change this field in the jiraissue table.  Actually the value in the jiraissues table is correct.   I understand why you might believe it to be wrong, but that is because of the way Jira is tracking user account renames.

Jira tracks a user account being renamed inside the app_user table.   When an account is first created in Jira the user_key field is set to match the cwd_user.user_name value.   However after this account is first created, that user_key field is never expected to change in the Jira database.  Regardless of whether or not the username changes, that user_key remains constant for that particular user account.  Instead the app_user.lower_user_name value can change to correspond with the changes in cwd_user table when the user gets renamed, but there are several places within Jira that do not directly refer to the cwd_user table for the user account.  Instead Jira is primarily looking at the app_user.user_key entry for historical records such as which user updated an issue, or executed a transition.   This can also be seen when you look at the issue details view in Jira, if you click on the history tab, this view shows the app_user.user_key field for a username, and not the cwd_user.user_name field.

This way Jira can keep track of historical changes that take place through a specific account, even if that account's name changes over time.  

As such I would not recommend trying to change this value in the database, but rather, it is better to refer to the app_user table to understand which account is really being used here.

Does this help?

If not, please let me know more about what exactly you are trying to achieve through SQL and perhaps I can offer another solution here.

Regards,
Andy

[Stefan Feldmann]

Thanks for the reply, that helped in my case. We have an internal dashboard with incident issue assigned to users. We have to load the user information from a different source and the world we'll be fine again.

[Richard Cross]

I'm unclear what the solution is to the problem this creates when a user whose username has change, is now consistently recorded under their old username in the issue history.

Is it expected that after a username change, we have to go into the database manually and update the corresponding LOWER_USER_NAME field in APP_USER?

[Andy Heinzer]

@Richard Cross No.  Doing that would break the historical tracking that ties the user account to changes made by that account, and probably break the account itself within Jira.  

The app_user table exists to map the user account data to the the historic user_key value. (Jira history fact: the app_user table doesn't even exist in Jira 5.x and earlier versions, because you couldn't rename accounts back then.  Account renaming in Jira first appeared in 6.0, and the app_user table appeared in that version to keep help keep track of changes).   That user_key value is expected to stay the same throughout the life of the account, even if it is renamed repeatedly. The app_user.lower_user_name value can change though.  It is expected to anytime the username changes on that account happen to the cwd_user.lower_user_name value.

There are several other SQL tables in the Jira database that refer to the user's user_key instead of the username, changegroup, searchrequest, come to mind first, but also lots of other tables use user_key instead for historical tracking of account actions.  

There isn't a great solution to this problem for existing accounts right now.  But there are indications that the user_key field will be changing the default values in upcoming releases for new users (away from using lowercase username in which that account was created with).  More details in https://confluence.atlassian.com/jiracore/gdpr-changes-in-jira-975041009.html#GDPRchangesinJira-userkeys

This should also help admins be more GDPR compliant as well.   It probably won't help existing user accounts that don't want to see their old 'username' (it's actually the user_key) in the history tab of an issue, but this is something that should prevent this pain for other users/admins in the future.

[Richard Cross]

Doing that would break the historical tracking that ties the user account to changes made by that account, and probably break the account itself within Jira.  

The historical tracking is already broken.  You've got a user 'janejones' (who was orriginally 'janesmith') who can no longer run JQL like:

assignee was 'janejones'

because the issue history have no record of that new username.  Every record in the history has 'janesmith', which also cannot be used in JQL because of the input validation. 

[Andy Heinzer]

That's the user_key field.  Not the username field.  Confusing, I understand because in your case the value of 'janejones' was the original username field, which makes it the user_key field as well.  But once that account is renamed, you can't search JQL by the account's old username nor the current user_key value.  JQL has never used the  user_key values to search on users.  Instead you have to use what the accounts current username value is in order to return issues that user account has touched.

[Eric Collins]

@Andy Heinzer, I have a situation where we change usernames in AD that syncs with crowd.  Within the history of a jira issue, the old username continues to appear. How do I purge the old username from ever appearing in the history of an issue? The new one must take place.  

[Andy Heinzer]

The history tab of an issue actually is displaying the user accounts 'userkey', and not the current username.  If the account has never been renamed, then the userkey will match the username.   However that userkey value is always expected to always be the same once the account is created.  There technically is not yet a supported way for you to change that userkey on the account.  This userkey is being used as a unique identifier for the account in Jira. 

There is a feature request to use the username in the history instead of the userkey over in https://jira.atlassian.com/browse/JRASERVER-61871 however this has not had a lot of interest so it hasn't really yet been considered.

Why are you wanting to change this userkey value?   Are you trying to remove the traces of an old account for the same of something such as GDPR requirements?  If so, then I'd recommend following the guides on https://confluence.atlassian.com/gdpr/jira-core-jira-software-and-jira-service-desk-server-and-data-center-gdpr-support-guides-949245619.html

Within these links are the Right to Erasure and specifically on the page there is a link called 'Changing the user_key (only when the user_key holds PD)'  This section provides some sample SQL scripts you could use in order to change the userkey directly in SQL.  I would recommend that you create a backup of you database before doing this and maybe test this on a staging server first to better understand how it will effect your production data.

[charlie benvenuti]

@Andy Heinzer I'm also interested in being able to change this. We have some employees that changed names and showing their old emails is insensitive. 

[Penny Ecaruan]

@Andy Heinzer we have this exact problem.  The problem is that even though we have updated multiple tables in SQL to user a new AD name instead of the old Internal Jira Directory name, we still have to use the "old" name in JQL searches, but in the body of the issue, the new name appears.  How do I update the JQL  searches so that they reflect the "NEW" username?  For example:  sbrown was updated to Sally.brown@ourcompany.com (and the JIRAUSER##### number is what was used to update the tables).  The issues inside shows sally.brown@ourcompany.com but to search the issues, we still have to use sbrown.  

Where do we update the sbrown in this case to get the search to work correctly????

It's very confusing for users and VERY frustrating for the Jira Admins!  Especially since ne

w issues are created correctly, so now you have to use two usernames to search even though the issue fields show the correct user.  I could bulk update to fix it, but you can't bulk update a Watcher!