Summary

When administering Jira instances, you may need to delete an existing user account, however, as per our documentation on Deleting Jira Users, a user can't be entirely deleted if it has lingering references in existing Jira issues, such as commenting or having assigned issues.

While assigned issues can easily be identified through a link provided during the deletion attempt, such a listing is not provided for comments, difficulting the removal process, should inactivating the account not be an option.

Example of user deletion attempt:

In the past, a suggestion was logged to implement this functionality in Jira, however, due to low traction, it was chosen not to be implemented into the main product. 

• JRASERVER-35774 - Deleting User: issue comments should have link

So this article aims to cover this gap and help administrators complete this task.

Environment

• Jira Data Center/Server.

Solution

Since no native function exists in JQL to specifically search for comments, we'll need to search for the comments directly in the Jira database to identify these lingering references.

To do so, we can utilize the select statement below while making sure to change <lowercase_username> the actual username (all in lowercase) that you are looking into deleting.

The SQL queries below were written and tested on a Postgres database, however, they can be easily adjusted to other database syntaxes should it be necessary.

SELECT ja.id as CommentID, CONCAT(pj.pkey, '-', ji.issuenum) as IssueKey, pj.pname as ProjectName, au.lower_user_name as CommentAuthor, ja.actiontype, ja.actionbody, ja.created
FROM jiraaction ja
LEFT OUTER JOIN jiraissue ji ON ja.issueid = ji.id
LEFT OUTER JOIN project pj ON ji.project = pj.id
LEFT OUTER JOIN app_user au ON ja.author = au.user_key
WHERE au.lower_user_name = '<lowercase_username>'
AND ja.actiontype = 'comment';

About the results

The above query should inform you of all issues/projects that contain a comment by a given user, allowing you, as the administrator, to delete the comments in the UI should you choose.

If the IssueKey returns empty (as a single '-'), that specific comment has lost its relationship with the comment still stored on the database (usually occurring due to a failure in the issue deletion process). In that case, you can delete the comment directly from the DB with the following DELETE statement (while adjusting the <CommentID> with the actual CommentID received in the previous results):

DELETE FROM jiraaction WHERE id in ('<CommentID>');

Note about deleting orphaned comments in the database:

• Atlassian recommends testing this process in a lower environment and taking a native database backup before executing this statement in production should rolling back the changes be necessary.
• We also recommend only deleting comments through the database when there's no relationship with an issue. If given the option, always remove the data through the Jira UI.