Move all JIRA associations from one user to another?

[Robert Withrow]

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? ;-)

[Stephen Crandell]

@Robert Withrow Not sure if you are using Cloud or Server instance but, on our Server instance we had a similar situation where we connect to a Microsoft Active Directory (MSAD) for the majority of our user base and create users in the Jira Internal Directory for subcontractors/client team members/ etc. The Jira Internal Directory is always first in the User Directory order list. We discovered that if you create a user in Jira Internal Directory with the same username as a user pulled in through the MSAD, Jira will allow it and all the MSAD users associations (assignee, reporter, comment author, change history, etc.) will be moved to the newly created user. 

In a recent situation, we had a MSAD user that has been working in our Jira instance for 8 years and a few days ago a junior admin created a new user with the same username in the Jira Internal Directory. So, all the associations were moved to the newly create user. Unfortunately for us, the junior admin realizing their mistake just changed the username for the new user, which cause Jira to move all those associations to the new username. You can't just change the username back because Jira will tell you that the username is already used (by the old MSAD user). UGH! I was able to move all the associations back with NO DB UPDATES and without having to even restart Jira by doing the following steps:

1. Disable the MSAD user directory
2. Update the Internal Directory username back to what it was, essentially causing the system to have two users with the same username again.
3. Enabled the MSAD directory again.
4. Delete the Jira Internal Directory user, which changes all the associations back to the old MSAD user
5. Create the new user again with a different username this time.

One additional note: We use our Jira instance as a user server for our Confluence, Bitbucket, and Bamboo instances and the above method worked to fix all the associations in those instances with a few extra steps:

1. Disable the MSAD user directory
2. Update the Internal Directory username back to what it was, essentially causing the system to have two users with the same username again.
3. Enabled the MSAD directory again in Jira.
4. Go to Confluence/Bitbucket/Bamboo and synchronize the Jira user server connection
5. Delete the Jira Internal Directory user, which changes all the associations back to the old MSAD user
6. Go to Confluence/Bitbucket/Bamboo and synchronize the Jira user server connection
7. Create the new user again with a different username this time in Jira.
8. Go to Confluence/Bitbucket/Bamboo and synchronize the Jira user server connection.

Don't know if this will help but, it worked for me and only took about 10-15 minutes!

[Tanya Koval]

@Stephen Crandell , how did you delete a user from Internal directory in step 4? Jira does not show duplicate usernames (only from MSAD).

[Stephen Crandell]

You have to be an jira system admin and

1. go to Administration -> User Management
2. Search for the user you want to delete
3. select the three dots on the right side
4. Select Delete User

[Vijay Khacharia]

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

[Norman Abramovitz]

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

[Robert Withrow]

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.

[Vijay Khacharia]

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')

[Robert Withrow]

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.

[Norman Abramovitz]

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.

[Norman Abramovitz]

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