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

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

4 answers

1 accepted

2 votes

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

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

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

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.

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. :)

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 ?

Chris Fuller Atlassian Team May 07, 2015

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.

Chris Fuller Atlassian Team May 07, 2015

(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.)

Chris Fuller Atlassian Team May 07, 2015

Correction: ... membership_type of GROUP_USER ...

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 ?

Chris Fuller Atlassian Team May 07, 2015

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

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

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.

 

 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,760 views 11 18
Join discussion

Atlassian User Groups

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!

Find my local user group

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

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot