How do you delete duplicate user ids after integrating with AD?

Hi there,

Does anyone know how to delete duplicate user ID's from Jira? I had about 60 users created in the internal jira directory. After connecting to AD and syncing the data, we found that those 60 users were duplicated (as they had the same logon id).

I'can't delete the acounts as they have issues assigned/reported to/by them.

I've tried the rename user script, by this does not work, looks like the duplicate id's cause the script to fail.

The other option I've looked at is deleting all the AD user ids from database (the trying rename), but in testing this did not work (I deleted them from the cwd tables - are there any others that need to be deleted), the users still appeared in the directory.

Would really appreciate some advise.

Thanks

2 answers

1 accepted

Accepted Answer
0 votes

I had a similar issue two years ago when we converted to an AD. Here are a number of statements I ended up using. I think you have to go through this for each user, replacing some of the constants with the applicable values. I don't think Atlassian supports this but they don't have a better answer either (at least didn't at the time).

select * from jiraowner.cwd_directory;

select * from JIRAOWNER.CWD_MEMBERSHIP where upper(child_name) = upper('a11950');

delete JIRAOWNER.CWD_MEMBERSHIP where id = 10292;

select * from JIRAOWNER.CWD_USER where upper(user_name) = upper('a11950');

delete JIRAOWNER.CWD_USER where id = 10236;

update jiraowner.cwd_membership set directory_id='10100' where child_name='a11950' and parent_name <> 'jira-users';

update jiraowner.cwd_user set directory_id='10100' where user_name='a11950';

delete JIRAOWNER.CWD_MEMBERSHIP where upper(child_name) = upper('a11950');

delete JIRAOWNER.CWD_USER where upper(user_name) = upper('a11950') and directory_id = '10100';

Thanks Alan - I'll give this a go.

Hi Alan, with the help of you SQL and running the rename script I was able to import the AD directory and then merge the required user id's from the internal directory with the AD accounts.

Thanks

0 votes

It depends on from which directory you want the user to exist and from which to not. You would need to remove the duplicated usernames in cwd_user table depending on its directory_id. Usually directory_id=1 being the Internal JIRA directory. Normally, when connecting to LDAP, users would want to remove the duplicated users in the Internal JIRA directory.

Anyway, if you have doubts, feel free in raising a support ticket in http://support.atlassian.com

Thanks for the advise - the idea is to use LAP, but have 1 local (admin) account in the Jira directory.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 18, 2018 in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

20,611 views 2 7
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