What are all the SQL Tables to be modified if we want to change a group of usernames ?

[Nithin CK]

Hi,

We have lot of usernames with their mail address and now we have to update the domain name of all those user accounts.

So we need the list of tables to modify for a successful username update ?

Best Regards,

Nithin CK

[Matheus Fernandes · Accepted solution]

Actually, there is also data on app_user. Still, I would not recommend doing this - rather, modify these usernames by editing the users in JIRA. If multiple users need to be edited, use a script to update them through REST: https://docs.atlassian.com/jira/REST/latest/#d2e4140

[Martin Cleaver]

Granted, Atlassian advises against the use of SQL to manipulate user tables.

In at least the case of https://answers.atlassian.com/questions/250787 and my case, using the UI (or REST) does not work (see https://jira.atlassian.com/browse/JRA-45203)

Tempo users had this problem here: https://tempoplugin.jira.com/browse/JTMPO-1100

Atlassian acknowledge this in https://jira.atlassian.com/browse/JRA-36642 - this is reported to be fixed in JIRA 7.

[Tim Eddelbüttel]

Hello Nithin,

what is you JIRA version?
I've done a complete rename of all JIRA users over 3 directories (internal, 2 different active directories). Some internal users where also in these active directories with different usernames. This was done against JIRA 6.0 and at this version there was no offical rename feature.

Regards,
Tim

[João Palharini]

Hi Nithin!

All information regarding users in JIRA (at least on the most recent versions) is distributed between two tables: {{cwd_user}} and {{cwd_membership}}.

--
Cheers!

Joao

[Nithin CK]

Hi Joao,

Thanks for the quick response.

Actually I modified the usernames in all these below tables and attributes in the image but still we faced few problems like dashboard was not accessible, filters were not available.

So we need to know if we have missed anything else in our procedure ?

SQL_Tables_List.jpg

~Nithin CK

[Matheus Fernandes]

Nithin, The thing is, not all of those will need to be changed. Most of those are associated with the user's user_key (from app_user) and not with the user_name from cwd_user. That's likely the reason this is breaking.

[Matheus Fernandes]

Those should not be changed, the user_key should always remains the same. In order to maintain the integrity of the database I'd definitely advise you to use REST or the UI. :)

[Nithin CK]

Hi Matheus, Thanks for the response. Can I get a proper list of those tables ? Also if I don't change the lower_user_name attribute on app_user table then will it not lead to some inconsistency ?

[crf]

You should use REST, as advised. Modifying the database directly when there is a perfectly good *supported* way to accomplish what you want is needlessly reckless. However, if you stubbornly and against all sane advice insist on manipulating the data directly, the impact should be limited to app_user.lower_user_name, the cwd_user name columns, and the cwd_membership child_name and lower_child_name columns for those with a membership_type of CWD_USER. All the others should remain the user's key -- that is the whole point of the rename user work we did in 6.0.

[crf]

(In other words, by changing all those others, you have effectively trashed your database. You should restore from a backup and use REST as was recommended.)

[crf]

Correction: ... membership_type of GROUP_USER ...

[Nithin CK]

Hi Chris & Matheus, Thanks for the help. Is REST a plugin and do we have a compatible REST plugin for Jira 6.1.1 version ?

[crf]

REST is a plugin, but it is included with JIRA, not one you would have to download and install separately. You can read more about what REST is and walk through a few tutorials about it here: https://developer.atlassian.com/jiradev/api-reference/jira-rest-apis