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

[Ash]

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

[Alan elfert · Accepted solution]

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]

Thanks Alan - I'll give this a go.

[Ash]

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

[Zul NS [Atlassian]]

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]

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

[Joy_Mather]

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