How to merge two users' content ownership and permissions in Jira

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Where a workaround involves running SQL scripts directly on your database, we strongly recommend you do this first on a non-production, staging or test environment before running the scripts on your production database. We also strongly recommend you take a backup of your data before making any modifications via SQL scripts.

Problem

When migrating users from one LDAP / Active Directory to another (such as during a company merge or domain migration), content ownership will not be migrated to the new usernames if the new user key and Unique identifier do not match the existing unique user key and Unique identifier. When the unique identifier for each user changes across directories, Jira has no idea how to map the old usernames to the new usernames.

Disclaimer

Atlassian support does not recommend following these steps unless absolutely necessary (i.e. the second directory has already been added and users have content under both usernames), as they require a lot of manual database manipulation with a lot of opportunity for error.

Instead, we recommend renaming the users to their new usernames in the existing LDAP/AD directory, performing directory sync in Jira, then adding the second directory to Jira. This will effectively preserve content ownership and permissions for each user in the new LDAP/AD directory.

Diagnosis

These steps are intended to fix Jira content ownership after an environmental change to LDAP, wherein the original directory is no longer used and the new directory will be used going forward. This only applies when migrating from one external directory to another, where the usernames change and both directories have already been added to Jira (meaning users have created content under both usernames).

For example:


OldNew
Usernamepedro.souza@atlassian.compsouza@newdomain.com
Unique identifier (objectGUID)123456a555666b


The Unique Identified is defined as this setting (objectGUID is the attribute in this example):

 This does not apply if the existing directory is modified and the usernames are updated, keeping the same unique identifiers. That type of change does not require these steps and after the next directory sync, Jira will see the updated usernames, assuming they still have the original unique guid/identifier.

Resolution

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

When renaming users, there are 5 fields in 3 tables that need to be updated. You will also need a temp table to map the old usernames to the new usernames. 

  • cwd_user table: user_name and lower_user_name columns
  • app_user table: user_key and lower_username columns


Please Ensure that all "lower" username columns are indeed all lower-case entries when entering the new usernames.

(warning) The queries below are written for PostgreSQL and may need syntax changes for other database flavors.

Step 1: Stop Jira

As we are manipulating data, the instance must be stopped before proceeding. This will prevent any new records from being created or modified during the update process below.

Step 2: Create a temp table to map old and new user keys


CREATE TABLE tmp_app_user_migration
(
old_user_key varchar(255),
new_user_key varchar(255)
);




Step 3: Update app_user table

Obtain the old & new user keys:

select user_key, lower_user_name from app_user

You will need to find a way to manually keep track of which usernames are old vs new here, and their keys.

For duplicate users (i.e. have an old and new username), insert their old_user_key and new_user_key:

INSERT INTO tmp_app_user_migration (old_user_key, new_user_key) 
VALUES
 ('<oldusername1>', '<newusername1>'),
 ('<oldusername2>', '<newusername2>'),
 ('<oldusername3>', '<newusername3>'),
 ...
 ('<oldusernameN>', '<newusernameN>');
  • Example:

    INSERT INTO tmp_app_user_migration (old_user_key, new_user_key) 
    VALUES
     ('pedro.souza', 'psouza'),
     ('marcus.silveira', 'malmeida');
    

Step 4: Migrate the content ownership for the new user_key:

Run the following queries to leave all the content associated to one of the user accounts:
(info) These updates should work for PostgreSQL, MySQL, and MSSQL. 

update audit_item set object_id = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_id = tmp_app_user_migration.old_user_key;
update audit_item set object_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_name = tmp_app_user_migration.old_user_key;
update audit_log set author_key= tmp_app_user_migration.new_user_key from tmp_app_user_migration where author_key= tmp_app_user_migration.old_user_key;
update audit_log set object_id = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_id = tmp_app_user_migration.old_user_key;
update audit_log set object_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_name = tmp_app_user_migration.old_user_key;
update changegroup set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
update changeitem set newvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where newvalue = tmp_app_user_migration.old_user_key;
update changeitem set oldvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where oldvalue = tmp_app_user_migration.old_user_key;
update component set lead = tmp_app_user_migration.new_user_key from tmp_app_user_migration where lead = tmp_app_user_migration.old_user_key;
update customfieldvalue set stringvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where stringvalue = tmp_app_user_migration.old_user_key;
update favouriteassociations set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
update fileattachment set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
update filtersubscription set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
update jiraaction set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
update jiraaction set updateauthor = tmp_app_user_migration.new_user_key from tmp_app_user_migration where updateauthor = tmp_app_user_migration.old_user_key;
update jiraissue set reporter = tmp_app_user_migration.new_user_key from tmp_app_user_migration where reporter = tmp_app_user_migration.old_user_key;
update jiraissue set assignee = tmp_app_user_migration.new_user_key from tmp_app_user_migration where assignee = tmp_app_user_migration.old_user_key;
update jiraissue set creator = tmp_app_user_migration.new_user_key from tmp_app_user_migration where creator = tmp_app_user_migration.old_user_key;
update os_historystep set caller = tmp_app_user_migration.new_user_key from tmp_app_user_migration where caller = tmp_app_user_migration.old_user_key;
update portalpage set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
update project set lead = tmp_app_user_migration.new_user_key from tmp_app_user_migration where lead = tmp_app_user_migration.old_user_key;
update projectroleactor set roletypeparameter = tmp_app_user_migration.new_user_key from tmp_app_user_migration where roletypeparameter = tmp_app_user_migration.old_user_key;
update searchrequest set authorname = tmp_app_user_migration.new_user_key from tmp_app_user_migration where authorname = tmp_app_user_migration.old_user_key;
update searchrequest set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
update worklog set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
update worklog set updateauthor = tmp_app_user_migration.new_user_key from tmp_app_user_migration where updateauthor = tmp_app_user_migration.old_user_key;
update userassociation set source_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where source_name = tmp_app_user_migration.old_user_key
	and sink_node_id not in (select U1.sink_node_id from userassociation U1, userassociation U2 
								where U1.source_name = tmp_app_user_migration.old_user_key and U2.source_name = tmp_app_user_migration.new_user_key
								and U2.sink_node_id = U1.sink_node_id);
update userhistoryitem set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key
	and entityid not in (select U1.entityid from userhistoryitem U1, userhistoryitem U2 
								where U1.username = tmp_app_user_migration.old_user_key and U2.username = tmp_app_user_migration.new_user_key
								and U2.entityid = U1.entityid
								and U2.entitytype = U1.entitytype);	

(info) For MySQL, the last two queries from above should be changed to the ones below:

update userassociation as UA, tmp_app_user_migration AS TA
set UA.source_name = TA.new_user_key
where UA.source_name = TA.old_user_key
and sink_node_id not in (select sink_id from (select U1.sink_node_id as sink_id from userassociation U1, userassociation U2, tmp_app_user_migration AS TA
where U1.source_name = TA.old_user_key and U2.source_name = TA.new_user_key
and U2.sink_node_id = U1.sink_node_id) as sink);


update userhistoryitem as UA, tmp_app_user_migration AS TA
set UA.username = TA.new_user_key
where UA.username = TA.old_user_key
and entityid not in (select entity_id from (select U1.entityid as entity_id from userhistoryitem U1, userhistoryitem U2, tmp_app_user_migration AS TA
where U1.username = TA.old_user_key and U2.username = TA.new_user_key
and U2.entityid = U1.entityid
and U2.entitytype = U1.entitytype)as entity);

Disclaimer

You may have AO_ tables provided by Plugins that may associate data directly in the user_key.

We recommend checking in the AO tables of your JIRA database and follow the same UPDATE model to adjust those tables.

Below you can find a list of the most common AO tables which are known to reference the user_key directly, you may include these tables in the migration process in case you have these tables in your instance database:

AO Tables list
AO_0A5972_NOTIFICATION_SETTING.USER_KEY
AO_0A5972_PUSH_REGISTRATION.USER_KEY
AO_21F425_MESSAGE_MAPPING_AO.USER_HASH
AO_21F425_USER_PROPERTY_AO.USER
AO_248DF5_INOTIFICATION.ACTION_USER_ID
AO_248DF5_INOTIFICATION.NOTIFIED_USER_ID
AO_248DF5_INOTIFIED_USER.USER_ID
AO_248DF5_INOTIFI_COUNTER.USER_ID
AO_248DF5_ITEMP_NOTIFI.USER_ID
AO_248DF5_ITEMP_NOTIFI.USER_KEY
AO_248DF5_IUSER_ADMIN_NOTIFI.USER_ID
AO_248DF5_IUSER_MESSAGING.USER_ID
AO_248DF5_IUSER_NOTIFI_SETTING.USER_ID
AO_2C4E5C_MAILCONNECTION.USER_NAME
AO_4789DD_READ_NOTIFICATIONS.USER_KEY
AO_4AEACD_WEBHOOK_DAO.LAST_UPDATED_USER
AO_4B00E6_SR_USER_PROP.USERNAME
AO_4C3E54_INBOXFIELDROLE.USER_SETTING_ENTITY_ID
AO_4C3E54_INBOXMENUTAGS.USER_ID
AO_4C3E54_INBOXMESSAGES.USER_ID
AO_4C3E54_INBOXREMINDERS.USER_ID
AO_4C3E54_INBOXUSERPROJECTS.USER_ID
AO_4C3E54_INBOXUSERSETTINGS.USER_ID
AO_54307E_CAPABILITY.USER_KEY
AO_54307E_ORGANIZATION_MEMBER.USER_KEY
AO_54307E_SERVICEDESK.CREATED_BY_USER_KEY
AO_54307E_SUBSCRIPTION.USER_KEY
AO_563AEE_ACTIVITY_ENTITY.USERNAME
AO_563AEE_ACTOR_ENTITY.USERNAME
AO_56464C_APPROVERDECISION.USER_KEY
AO_56464C_NOTIFICATIONRECORD.USER_KEY
AO_587B34_GLANCE_CONFIG.APPLICATION_USER_KEY
AO_589059_AUDIT_ITEM.AUTHOR_KEY
AO_589059_AUDIT_ITEM_ASC_ITEM.ASSOC_ITEM_ID
AO_589059_AUDIT_ITEM_ASC_ITEM.NAME
AO_589059_RULE_CONFIG.ACTOR_KEY
AO_589059_RULE_CONFIG.AUTHOR_KEY
AO_5FB9D7_AOHIP_CHAT_LINK.SYSTEM_USER
AO_5FB9D7_AOHIP_CHAT_LINK.SYSTEM_USER_TOKEN
AO_5FB9D7_AOHIP_CHAT_USER.HIP_CHAT_USER_ID
AO_5FB9D7_AOHIP_CHAT_USER.HIP_CHAT_USER_NAME
AO_5FB9D7_AOHIP_CHAT_USER.USER_KEY
AO_5FB9D7_AOHIP_CHAT_USER.USER_SCOPES
AO_5FB9D7_AOHIP_CHAT_USER.USER_TOKEN
AO_5FB9D7_AOHIP_CHAT_USER.USER_TOKEN_EXPIRY
AO_60DB71_AUDITENTRY.USER
AO_60DB71_BOARDADMINS.KEY
AO_60DB71_RAPIDVIEW.OWNER_USER_NAME
AO_733371_EVENT.USER_KEY
AO_733371_EVENT_PARAMETER.VALUE
AO_733371_EVENT_RECIPIENT.USER_KEY
AO_7DEABF_CUSTOM_FIELD.CREATED_BY
AO_82B313_PERSON.JIRA_USER_ID
AO_97EDAB_USERINVITATION.SENDER_USERNAME
AO_9B2E3B_EXEC_RULE_MSG_ITEM.RULE_MESSAGE_VALUE
AO_9B2E3B_PROJECT_USER_CONTEXT.USER_KEY
AO_9B2E3B_RSETREV_USER_CONTEXT.USER_KEY
AO_9B2E3B_RULE_EXECUTION.EXECUTOR_USER_KEY
AO_A0B856_WEB_HOOK_LISTENER_AO.LAST_UPDATED_USER
AO_C7F17E_PROJECT_LANG_REV.AUTHOR_USER_KEY
AO_CFF990_AOTRANSITION_FAILURE.USER_KEY
AO_D530BB_CANNEDRESPONSE.CREATED_USER_KEY
AO_D530BB_CANNEDRESPONSE.UPDATED_USER_KEY
AO_D530BB_CANNEDRESPONSEAUDIT.USER_KEY
AO_D530BB_CANNEDRESPONSEUSAGE.USER_KEY
AO_D9132D_PERMISSIONS.HOLDER_KEY
AO_D9132D_PLAN_USER_PROPERTY.USER_KEY
AO_D9132D_PLAN_US_PR_MAPPING.USER_ID
AO_D9132D_SCENARIO_ABILITY.LAST_CHANGE_USER
AO_D9132D_SCENARIO_ISSUES.LAST_CHANGE_USER
AO_D9132D_SCENARIO_ISSUE_LINKS.LAST_CHANGE_USER
AO_D9132D_SCENARIO_PERSON.LAST_CHANGE_USER
AO_D9132D_SCENARIO_PLAN_CAP.LAST_CHANGE_USER
AO_D9132D_SCENARIO_RESOURCE.LAST_CHANGE_USER
AO_D9132D_SCENARIO_SKILL.LAST_CHANGE_USER
AO_D9132D_SCENARIO_STAGE.LAST_CHANGE_USER
AO_D9132D_SCENARIO_TEAM.LAST_CHANGE_USER
AO_D9132D_SCENARIO_THEME.LAST_CHANGE_USER
AO_D9132D_SCENARIO_VERSION.LAST_CHANGE_USER
AO_D9132D_SCENARIO_XPVERSION.LAST_CHANGE_USER
AO_E8B6CC_ORGANIZATION_MAPPING.ADMIN_USERNAME
AO_E8B6CC_PROJECT_MAPPING.USERNAME
AO_E8B6CC_PROJECT_MAPPING_V2.ADMIN_USERNAME
AO_E8B6CC_PR_PARTICIPANT.USERNAME

Step 5: Start JIRA

Start Jira and rebuild the index, so it can take effect.

  • We recommend leaving the temp tables in place, as they may be useful in the future. 

Optional:

In case this procedure has left empty leftover users that are no longer required and you would like to completely remove these empty user profiles, eg: Internal Users that are no longer needed after merging the profiles.
You can follow the additional procedure below to completely delete users from the database: Delete users from the database


Last modified on Sep 11, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.