Delete user from database in Jira server

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Problem

It is not possible to delete a user from JIRA because the user is associated with several issues. 

Cause

The user might be a reporter, assignee or commented on several issue so JIRA will not allow you to delete the user until you have removed all the user's associations.

Workaround

It is possible, but not recommended, to forcefully delete this user from the Database without removing the user from the associated issues

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

  1. Stop JIRA. Open the database and run the following :

  2. Run this SQL

    select * from cwd_user where user_name = 'xxxxx';
    select * from app_user where lower_user_name in (select lower_user_name from cwd_user where user_name = 'xxxxx');

    Replace xxxxx with the affected username. Case sensitive!

  3. Please take note of the *id* values of this user in both tables for step 4. The queries might return multiple rows if there are multiple directories with associated with the user, so you might need to take note of multiple ids.

  4. Run this SQL to delete the cwd_user_attributes of the user

    delete from cwd_user_attributes where user_id='xxxxx';

    (i) Replace xxxxx with the *id* from step 1&2

  5. Run this SQL to delete the cwd_membership of the user

    delete from cwd_membership where child_name='xxxxx';

    (i) Replace xxxxx with the affected username


  6. Run this SQL to delete the cwd_user of the user

    delete from cwd_user where user_name ='xxxxx';

    (i) Replace xxxxx with the affected username

  7. Run this SQL to delete the user from app_user

    delete from app_user where id ='xxxxx';

    (i) Replace xxxxx with the id from step 2 that you got for the app_user table


  8. Start JIRA

This will remove the username from all 4 tables :

  • cwd_user_attributes
  • cwd_user
  • cwd_membership
  • app_user


And therefore , removing the user completely from the JIRA Internal Directory. Please remember to restart JIRA after performing the changes from the database

DescriptionIt is possible, but not recommended, to forcefully delete this user from the Database without removing the user from the associated issues
ProductJira
PlatformServer
Last modified on Sep 25, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.