How to edit AD imported user which is deleted in AD?

Jacek Nowak_w3ly8z

Hello everyone!

Let me explain the situation. We identify users in our company using a code in format:
ABC1234. Sometimes (rarely but still...) it may be that we have to change that code. In that case completely new account is created. It has the same name and surname and in some cases the same email.

In Jira we are using incoming mail handlers in order to create tickets from email. Here is the problem... After we create new user in AD with the same email it's imported to Jira of course. Then old user is deactivated in AD and finally after 1 month it is deleted completely. We assume that deleted user did some work inside Jira, e.g. reported or was assigned to a task. Jira keeps both accounts and they have exactly the same email. For some reason email handler checks the status of the oldest user (which is inactive in Jira) and throws an error in logs that ticket could not be created because the user has no access to Jira.

I've already found some questions on the community on that topic. They all propose changing an email of inactive user as a solution. I'm okay with that but I can't do anything with this user because he is imported from active directory. I've tried AD resynchronization in Jira, changing an email in database but with no success.

This is my situation right now. Two users, both imported from AD.

First user (was created earlier, inactive):
Username: ABC1234
Full name: John Doe, (john.doe@company.com) [X]
Email: john.doe@company.com

Second user (new user created recently, active):
Username: CBA4321
Full name: John Doe, (john.doe@company.com)
Email: john.doe@company.com

First user is no longer present in Active Directory, he was deleted. However his account in Jira has some ticket "connections". He wrote some comments and created a few issues.

Andy Heinzer

Hi Jacu,

I see that you are looking to change the email address of an AD/LDAP user in Jira.  However even direct SQL changes to that record appear to fail this purpose.  I think I can shed some more light on the problem here.

Active Directory may have called that user object 'deleted' however this status is somewhat misleading because the object technically still exists Active Directory, but the user cannot utilize any of the services Active Directory previous granted to them.  Moreover, when Jira is syncing to this directly, it still can see that deleted object and sync data from it.  Which is why after you try to manually change the email address in the database, Jira appears to be changing that email address back to what it was.  This detail is noted in the Username section of the documentation in Connecting to an LDAP directory.

I found a pair of interesting guides in regards to recovering that object in Active Directory:

• A Step-By-Step Guide to Restore Deleted Objects in Active Directory
• How to restore deleted user accounts and their group memberships in Active Directory

You, as a Jira Admin, might not be the user that can follow these steps, so you might actually need to get your AD admin to try these steps.   If the AD admin could temporarily restore that object in AD, then change the email address to some different value like 'username@example.com'.  Jira could then sync that change for that account.  Which would then allow you to have the new account be the only one with the desired email address in Jira.

That is probably the method that makes the most sense and requires the least amount of hacking into Jira.  That said, I can see another alternative method where you might be able to adjust this in Jira's SQL database.

Jira has a table in the database called app_user.  This table has existed since Jira 6.0.  It specifically exists to keep track of user account renames in Jira. This table only has two columns: user_key and lower_user_name.  When an account is created in Jira for the first time, each account is generated a unique user_key value in that table.  Right now the default behavior (In Jira 8.4 and below) has been to use the lower_user_name value at the time this account is created to be that user_key, and if that value is already taken, append a #1 to that name (or increment that value, #2, #3, etc).  Jira does not expect to change the user_key value ever, even if the username changes.   The value in lower_user_name in that table can change though, and it will change anytime the user account is renamed.  It will then be expected to match the cwd_user table and the lower_user_name value in that table.

With that info in mind, you could make an adjustment to the app_user table here instead.  There is some risk in doing this, especially in regards to the historic account actions.  Many functions in Jira record the user_key of an action instead of the user_name, so to make a change to the user_key field does have the potential to alter the historic account actions.   So if you go this route, definitely create a back of your SQL database first, and I would also suggest testing this change in a staging environment before trying it in a production site.  It might be a viable work-around, but being there exists potential to alter historic info in Jira, perhaps the first option would be better to approach instead.

Let me know if you have any concerns about either option here.

Andy