Where are all JIRA accounts stored in database?

I'm trying to pull all Jira accounts from database. Where can I find exactly the same account list as through the User Management in Jira?

Distinct from cwd_user shows twice as much user accounts as User Management.

Distinct from external_entities shows ~30% more user accounts than User Management.

1 answer

1 accepted

1 vote

It's cwd_user.

I suspect your "distinct" might be confusing things, although you have not told us what you're doing with it - users can be duplicated across directories with the same name, which could well explain what you're seeing.

As an example, I set up a client's JIRA recently.  I'm nic in the internal directory.  I'm nic in their main site's internal directory, I'm nic-s in another site's directory and I'm nic in the Adaptavist directory.  Doing a "distinct" based on the login id will give you 2 answers, which is wrong.


Nic, I have used distinct exactly to avoid getting many records for users that come from multiple user directories :

select distinct lower_user_name
from cwd_user

Still the query shows many accounts that are not visible in User Management.


OK, I have found what's wrong. cwd_directory shows 7 directories. Those are the same directories that I can see in Jira in User Management. However when I selected:

select distinct directory_id
from cwd_user

I got 8 directories. It seems that on the beginning of the instance someone created a user directory and removed it right away. When I excluded this directory_id from search I got exactly the same number of users as in User Management in Jira.

That makes sense - although the "distinct" is wrong because it's hiding user accounts, the users JIRA sees are masked - in my case, the Adaptavist nic is the one of the three that is used because that directory is higher than the others.

An extra directory would explain your results.

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Feb 15, 2018 in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

1,143 views 6 19
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot