I have the following problem when renaming a username for an existing user.
I have created a new user, for instance username 'email@example.com' and email also 'firstname.lastname@example.org'.
After that I created issues where this user email@example.com is the ASSIGNEE.
Then I renamed the username from 'firstname.lastname@example.org' 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 'email@example.com' in a user field, for instance ASSIGNEE in the table jiraissue.
The table dbo.jiraissue still has the wrong value in the field ASSIGNEE: firstname.lastname@example.org.
My question now is, how can I correct this field (reference) in dbo.jiraissue?
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.
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?
@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.
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.
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.
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...
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