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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Jira Cloud Migration to Data Center results to usernames being their AccountID rather than a normal



we are migrating our Jira Cloud site to be a Data Center site. 



After importing the backup file, and accessing Jira as the "sysadmin" user mentioned in the documentation, we found that all migrated users from cloud Jira have their usernames as their "AccountID". 


We tried to update the usernames in the DB after stopping Jira, but whenever we update a username for user we find that it shows that the user doesn't exist in Jira. 


The question is

How to update all the usernames in the cwd_user table in the DB without corrupting the users in Jira. 


(We are thinking of deriving the usernames from the users email where the usernames will be whatever comes before the '@' sign ex: ===> username: qusai.atoon)

2 answers

1 accepted

0 votes
Answer accepted


After the answer of @Don Masten, I looked into the table that he mentioned, and I found that "lower_user_name" and updated it with the new usernames and the problem with authenticating to Jira was solved. 

But there was another issue that have appeared. 

The new problem was that all the users that were updated in the DB weren't in their groups (Authentication works, but Authorization is broken)


The solution to the authorization problem was to feed the table "cwd_membership" the new usernames in the columns "child_name" and "lower_child_name". 


we're using the DB: MS SQL Server 2019 and below are the queries that solves our problem:

/*1. Update the table [cwd_user] that contains all the users that are migrated to the jira cloud.*/

UPDATE jjddbb.jiraschema.cwd_user
SET jjddbb.jiraschema.cwd_user.user_name = email_address, jjddbb.jiraschema.cwd_user.lower_user_name = lower_email_address

/*2. update the table named [app_user] that also contains the users with their lowercase usernames. */

UPDATE jjddbb.jiraschema.app_user
SET jjddbb.jiraschema.app_user.lower_user_name = jjddbb.jiraschema.cwd_user.lower_user_name
FROM jjddbb.jiraschema.app_user
jjddbb.jiraschema.cwd_user.EXTERNAL_ID = [jjddbb].[jiraschema].[app_user].[user_key]

/*3. now we need to update the membership table.*/

UPDATE jjddbb.jiraschema.cwd_membership
SET jjddbb.jiraschema.cwd_membership.child_name = jjddbb.jiraschema.cwd_user.user_name, jjddbb.jiraschema.cwd_membership.lower_child_name = jjddbb.jiraschema.cwd_user.lower_user_name
FROM jjddbb.jiraschema.cwd_membership
INNER JOIN jjddbb.jiraschema.cwd_user
jjddbb.jiraschema.cwd_user.ID = jjddbb.jiraschema.cwd_membership.child_id

Kindly note that editing the DB records directly is not recommended and you will need to make sure that these changes won't impact the Jira instance that you are running so please before doing so back up you Jira application and you DB and turn off your Jira instance (if there are no other solutions to your problem other than modifying records directly in the DB)

I hope this is clear enough to help someone with a similar case in the future.  

1 vote
Don Masten
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Apr 26, 2023 • edited

It's been about 2 years since I've done it. Think you need to run the sql in step 8

I also manually updated the user names in the admin menu.  There is a user mapping in app_user table but would recommend not updating anything there.

Hello Don, 


The 8th step is for the interface where the users are referred to with their AccountIDs, which isn't my problem. 


My problem is that when a user logs in, they should use their AccountID, which is not convenient at all. 

I want them to login with their new usernames (the ones derived From the Email). 


Which I can do using an SQL query that changes the value of each username and lower_username to the  new username, but that corrupted the users. 

Does the table "app_user" that you mentioned in the answer exist in the DB? 

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events