Problem

The Jira UI does not currently provide a way to bulk delete groups.  If a customer has mistakenly created 100s of groups due to a mistake in configuring their LDAP filter, there is no easy way to remove them.  If you take proper care, SQL commands can be used to remove the unwanted groups.

When making the following actions...

• It's essential to shut down Jira while deleting the groups to prevent your users from encountering errors.
• A restart is also necessary to reset the in-memory caches of users and groups.
• If you use LDAP or Crowd for user management, the groups will be re-created on the next sync unless you modify your LDAP filter and groups in Crowd.

• Our warning about testing SQL queries on a test installation is critical here. Once you delete groups or group memberships, they are irrevocably gone.

Diagnosis

• You have 50+ groups in Jira that interfere with your ability to manage group membership.

Cause

Incorrect LDAP sync, extensive organization restructuring, accumulation of groups over time.

Workaround

First, please check whether you are using these groups in the UI (JIRA_BASE_URL/secure/admin/user/GroupBrowser.jspa) to make sure you remove them.

Then, use SQL to make additional checks. If any of these queries return records, shorten the list to identify the exact groups in use.

These queries have been tested in Postgres, MySQL, and OracleDB. You may need to modify the syntax for other supported databases.

IMPORTANT: The user and group names in the queries are case-sensitive.

 -- List current members of the groups.
SELECT child_name AS username
FROM cwd_membership
WHERE parent_name IN ('group-to-delete', 'another-group-to-delete');

-- Check for use of the groups in global permissions.
SELECT
  group_id AS groupname,
  permission
FROM globalpermissionentry
WHERE group_id IN ('group-to-delete', 'another-group-to-delete');

-- Check for use of the groups in project permissions.
SELECT
  p.pname               AS projectname,
  pr.name               AS rolename,
  pra.roletypeparameter AS groupname
FROM projectrole pr
  JOIN projectroleactor pra
    ON pr.id = pra.projectroleid
  JOIN project p
    ON pra.pid = p.id
WHERE pra.roletype = 'atlassian-group-role-actor'
      AND pra.roletypeparameter IN ('group-to-delete');

When you are sure it is safe to remove the groups, run these scripts in this order.

-- Remove all members of the groups.
DELETE FROM cwd_membership
WHERE lower_parent_name IN ('group-to-delete', 'another-group-to-delete');

-- Remove the groups from global permissions.
DELETE FROM globalpermissionentry
WHERE group_id IN ('group-to-delete', 'another-group-to-delete');

-- Remove the groups from project permissions.
DELETE FROM projectroleactor
WHERE roletype = 'atlassian-group-role-actor' AND roletypeparameter IN ('group-to-delete', 'another-group-to-delete');

-- Remove the groups.
DELETE FROM cwd_group where group_name IN ('group-to-delete', 'another-group-to-delete');