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

Ash April 19, 2013

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

1 vote
Answer accepted
Alan elfert April 20, 2013

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

Ash April 21, 2013

Thanks Alan - I'll give this a go.

Ash April 22, 2013

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
Zul NS _Atlassian_
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 19, 2013

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

Ash April 21, 2013

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

Joy_Mather June 23, 2019

This ticket is old but it is an issue which has now arisen for us.  Is your solution still an appropriate one?

Suggest an answer

Log in or Sign up to answer