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!


Jira LDAP Migration using SQL

If you have to migrate your Jira user population from one LDAP domain to another it can be quite a hairy process with all sorts of gotchas:


  • The user names don’t match in both domains
  • And neither do other properties such as full names or emails.
  • The domains could have management policies that prevent you from adding other properties to entries
  • Users have left and their entries in both domains are disabled
  • The work on migration on the domains themselves is a moving target outside of your control
  • The cutover from one domain to another cannot have the Jira server down for an extended period
  • The user would also like a new unicorn


In this case I chose to use SQL to re-map all user entries from old to new values. It works! But as with all Jira database interventions there are caveats

  • If you break Jira it’s all on you, not support
  • Make sure you have backups before starting and know how to restore them
  • Are you comfortable with DB tasks
  • make sure Jira service is not running - it won’t be happy
  • Do this several times in a test system and test the results - comparing filter results may be sufficient. We got many errors initially and refined queries to massage them out.

You will also need to know how long this will take for planning purposes.

There is a conventional approach described in this knowledge article

I wish I’d read that first! But we would still have had issues to resolve. In it, approach one assumes you are able to request property changes - I wasn’t. Approach two requires scripting to deal with volumes - which was an option.

I will document how I approached it. I hope the SQL supplied will give you other ideas for approaches or will help you generate data for scripting. SQL examples are for  MySQL database. You will have to change the syntax slightly for other databases.

Tables with user id values are:

  • jiraissue
  • customfieldvalue
  • projectroleactor
  • userassociation
  • filtersubscription
  • searchrequest
  • favouriteassociation
  • worklog


I did have the data from the LDAP dept that mapped old names to new names which was used to drive all the SQL statements.

Data Mapping

Create a temp user lookup table to use in updates

create table if not exists userxref
(olduser VARCHAR(30), newuser varCHAR(30));

use the mapping data to generate insert statements. 


INSERT INTO userxref VALUES ('olduserval', ‘newuserval');

 I used excel formulas for generation as the mapping data was supplied as a spreadsheet.

From this point onwards Jira must be shut down.

Jira standard user fields

SET SQL_SAFE_UPDATES = 0; -- allows sql updates without primary key value

update jiraissue 
set jiraissue.reporter= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.reporter=userxref.olduser),

set jiraissue.assignee= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.assignee=userxref.olduser),

set jiraissue.creator= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.creator=userxref.olduser),

 Jira custom user fields

You will need to identify the id’s of all custom user picker fields

SELECT * FROM jiradb.customfield
where customfieldtypekey in (‘com.atlassian.jira.plugin.system.customfieldtypes:multiuserpicker’,'com.atlassian.jira.plugin.system.customfieldtypes:userpicker')

 Also check if you have any plugin user fields. I didn’t.

Use the list of customfield id’s in the following statement:

set stringvalue= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where stringvalue=userxref.olduser),
where customfield in (11000,110001)

 For Project Roles

set roletypeparameter= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where roletypeparameter=userxref.olduser),

 For user associations ( filters, dashboards)

set source_name= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where source_name=userxref.olduser),

For Filter Subscriptions 

set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),

 For Filter owners

set authorname= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where authorname=userxref.olduser),

 set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),

Filter definitions are more involved as they as xml clobs. The update statement should take the general form:

 set reqcontent= replace(reqcontent, 'oldnameval','newnameval')
 where reqcontent like ‘%oldnameval%'


This SQL can be generated from the user mapping temp table

 ' update 
 set reqcontent= replace(reqcontent,''',olduser,''',','''',newuser,
''') where reqcontent like ''%',olduser,'%'';') as query
 from userxref;

 and then run the output.

For Favourites

set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),

 For Worklogs

set author= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where author=userxref.olduser),
set updateauthor= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where updateauthor=userxref.olduser),

For group membership updates I used REST calls.

First I created a view for group memberships

 drop view groupmap;

create view groupmap
as select parent_name
, ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where us.user_name=userxref.olduser),
 us.user_name) as newval
from cwd_membership mem
join cwd_user us on = mem.child_id

Then use that view in a query to generate curl statements to call the REST API.

'curl -insecure -u admin:password -X POST --data "{\\"name\\": \\”',newval,'\\"}" -H "Content-Type: application/json" https://localhost:8080/rest/api/2/group/user?groupname=', parent_name
) as curl
FROM jiradb.groupmap
where user_name != newval


curl -insecure -u admin:password -X POST --data "{\"name\": \”newuserval\"}" -H "Content-Type: application/json" https://localhost:8080/rest/api/2/group/user?groupname=jira-administrators

 Existing users were left in place as they would not count towards licence one the old domain was disabled.


At this point:

  • Jira can be restarted
  • Check logs for any unexpected errors - dry running in dev with recent backups should create a smooth process fro production changes.
  • Disable old domain.
  • Reindex Jira to pick up data changes.
  • Run any comparison filters you may have prepared

Voila! Let users loose on the new world.





Log in or Sign up to comment

Are comments on issues also adressed with those statements shown above?

Tom Lister
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Mar 29, 2019 • edited

Hi @congstar GmbH 

I recently used SQL above to migrate a cloud service desk system to hosted server.

I needed the following SQL's. Comments are stored in Jira Actions.

update jiraaction set author= coalesce(( select distinct coalesce(newuser, olduser) from userxref where author=userxref.olduser), author);

update "AO_54307E_ORGANIZATION_MEMBER" set "USER_KEY"= coalesce(( select distinct coalesce(newuser, olduser) from userxref where "USER_KEY"=userxref.olduser), "USER_KEY");

update os_historystep set caller= coalesce(( select distinct coalesce(newuser, olduser) from userxref where caller=userxref.olduser), caller); 

update os_historystep set owner= coalesce(( select distinct coalesce(newuser, olduser) from userxref where owner=userxref.olduser), owner); 
Like # people like this
AUG Leaders

Atlassian Community Events