Move all JIRA associations from one user to another?

A long time ago and about 20 JIRA versions away I made the silly admin mistake of having my username in the internal JIRA directory AND a CROWD directory. With the same password of course, so that I could never tell which directory I was logged into.

Well, I would sometimes switch the order of the directories for one reason or another. As a result about half of my JIRA stuff is associated with the one ID and half with the other. BUMMER!

Now I want to clean up this mess. I renamed the userid in the internal directory and tried to delete it, and cleaned up the stuff I could. Some of it is easy like project and component lead assignments, and reporter and asignee for open issues. But then you get to the hard stuff:

Delete User: xyzzy

Cannot delete user. 'xyzzy' has associations in JIRA that cannot be removed automatically.

Change the following and try again:

    Reported Issue: 639 issues
    Assigned Issue: 234 issues
    Issue Comments: 963

The reported and assigned changes are prevented by workflow constraints. I don't even get the option to change comment owners.

I feel like this is a legitimate thing to do because it really is me who reported and commented all those times. I know other people want to do this for less legitimate reasons like deleting users who leave the company.

So how do I fix these up? I can see two ways:

  • Do a XML backup and restore with XML foo in the middle.
  • Do SQL foo.

Suggestions? Working code? ;-)

3 answers

This widget could not be displayed.

I would look at the sql option and experiment on a copy of the database.

I am bit surprised you cannot do a bulk edit to change the assignee field though.

The reporter and assignee should be in the jiraissue table.

https://developer.atlassian.com/display/JIRADEV/Database+Schema#DatabaseSchema-IssueFields

This widget could not be displayed.

You will also need to update the change history tables as well.

Also check out the following answer as well.

https://answers.atlassian.com/questions/42347/how-to-bulk-change-assignee-or-bulk-change-reporter-to-another-user

The table jiraaction should contain your comments and author field should be what you want.

This widget could not be displayed.

Hi,

I think the change of assignee and reporter is not allowed as the issues might be closed and there is a condition on your workflow that doesnt allow change of Assignee and reporter in status closed.

As Norman suggested, you can update the DB directly to solve the issue. Here are the queries you can run for oracle database.

update jiraissue set assignee=<???> where assignee ='xyzzy'
update jiraissue set reporter=<???> where reporter='xyzzy'
update jiraaction set author = <???> where owner='xyzzy'

Vijay

In the previous answer to this question which I pointed you to, you could change the workflow and allow editing in the closed state.

https://confluence.atlassian.com/display/JIRA/Allow+editing+of+Closed+Issues

The answer I want to work (the SQL method) doesn't because none of the fields (assignee, reporter, etc) have the renamed userid ('xyzzy'). I can see the changed userid in the cwd_user table but apparently the associated issues don't get these fields updated. Maybe there is some other table involved? For example I see the app_user table which looks suspicious.

Hi,

You got it right I think, its in app_user table. Here updated sqls.

update jiraissue set assignee=<???> where assignee = (select user_key from app_user where lower_user_name = 'xyzzy')
update jiraissue set reporter=<???> where reporter = (select user_key from app_user where lower_user_name = 'xyzzy')
update jiraaction set author = <???> where author = (select user_key from app_user where lower_user_name = 'xyzzy')

All of the above approaches have drawbacks. For example, modifying the app_user table also affects authentication and other aspects of the database. Modifying all the workflows is inefficient and problematic (the work flows are modified for a period of time after all). I don't think this question has a straight-forward answer right now. I've moved some aspects of the issues using the standard in-app processes (queris and bulk modifications) and then disabled the old user which forces other changes to happen as issues get modified.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

573 views 25 15
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