Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

Recognition

  • Give kudos
  • My kudos

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Wrong user_name reference when renaming username

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

2 answers

1 accepted

3 votes
Answer accepted
Andy Heinzer Atlassian Team Nov 06, 2017

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

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.

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 Atlassian Team Sep 09, 2019

@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.

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 Atlassian Team Sep 12, 2019

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.

@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 Atlassian Team Oct 01, 2018

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.

@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. 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

How to create Jira issus from Excel file?

When to use CSV importer When managing your processes in Jira, there are many occasions where you need to create a lot of tasks. Creating them one by one will cost you a lot of time and effort and i...

4,623 views 22 33
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you