Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

SQL Query to find the projects with inactive leads


I need SQL query to find the list of the projects which have inactive project leads in it.




1 answer

1 vote
Sreenivasaraju P
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 21, 2018



SELECT * FROM project WHERE lead NOT IN (SELECT user_name FROM cwd_user WHERE active = 1);

Pleaase refer 

below link

Hello Sree, 


Thanks for your reply but above query will be the list of those project where project lead is not assigned. 

Here I am looking for list of those project were project lead is inactive. 



Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
Nov 21, 2018

You could modify that query to eliminate the projects without a lead and only show you ones where the lead user account is inactive in Jira with something like:

SELECT * FROM project WHERE lead IN (SELECT user_name FROM cwd_user WHERE active = 0);

But I can also see a problem with this query.  It's returning the user_name field form the cwd_user table.  That will probably work for most cases, but it's possible that user accounts in jira could have been renamed at some point.  If that happens, the lead field in the project table won't match the user_name field in the cwd_user table.  Which is why we should look at the app_user table's user_key field to see which account corresponds correctly to that project lead field.

So I think a more accurate SQL query for what you want would be:

SELECT * FROM project WHERE lead IN (
select user_key from app_user au
join cwd_user cu on cu.lower_user_name=au.lower_user_name
Like # people like this

@Andy Heinzer Is there any way to do this with either Groovy or Automation for Jira.....or just plain REST API?

We are spending quite some support time on reviewing for inactive Project Leads on a monthly basis, hence we do not allow Projects without an ownership and have numerous other side effects to it(Dead projects/issues, Cannot delete the user, Archived Projects etc.)

Would really like an Autoamtion rulr or script that just:

  1. Loop over all Projects in Jira
  2. Check for Project Leads = Inactive
  3. Inform Support, that they need to contact Project/Project Admins

An alternative method, is to loop over all inactive users and check if they are Project Leads on any projects.

Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 05, 2023

@Morten Stensgaard Not exactly.  This original post was in regards to Jira Server, which implies that the administrators potentially have direct access to the SQL database that Jira uses.

Jira Cloud doesn't provide SQL access for customer, so the answers above don't really apply to Cloud.

Automation might be able to help, but it's not well suited for doing a lookup on project settings.  Instead, you might have more success just having it do a Scheduled trigger that runs a JQL query, such as

assignee in inactiveUsers()

Which should then return to you all the issues in Jira that have an assignee of any issue.  If that's too broad, then perhaps something more like:

assignee in inactiveUsers() and statuscategory!=done

to find assigned issues to inactive users where the issue is not in a done category status. 

Suggest an answer

Log in or Sign up to answer