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

[Qusai Atoon]

Hello, 

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

Problem:

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: qusia.atoon@gmail.com ===> username: qusai.atoon)

[Qusai Atoon · Accepted solution]

After my experience with Atlassian products for a couple of years, I would advise that you don't use my proposed solution mentioned above, since updating the database will create some side affects that we don't know the affects of. 

The problem here is so simple, the usernames can be just mapped with the emails or some derivation form the display name of the user, do what suites you, but always use the REST API of the application & don't update the DB records directly from the DB since the REST APIs actions are validated and we can't corrupt the data in the application. 

Note: There are some cases where Atlassian will advise us to update the DB directly, then it's ok since they're the ones who created the DBs and there dev teams will know every affect of updating the DB directly. 

[Don Masten]

It's been about 2 years since I've done it. Think you need to run the sql in step 8
https://confluence.atlassian.com/adminjiraserver/migrating-from-jira-cloud-to-server-applications-938846963.html

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.

[Qusai Atoon]

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. 

[Qusai Atoon]

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

[Qusai Atoon]

Hello,

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 
INNER JOIN 
jjddbb.jiraschema.cwd_user
ON 
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
ON 
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.