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.
@Andrew 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.
...PermissionsStartOnly=true User=www-data Group=www-data ExecStart=/opt/jira/bin/startup.sh ExecStop=/opt/jira/bin/shutdown.sh TimeoutStartSec=120 TimeoutStopSec=600 PrivateTmp=true [Install] WantedBy...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs