JIRA SQL selects to find user details

When administrating customer's JIRA instances, it is often useful to display certain data by running an SQL select, instead of logging in to the JIRA instance (the server administrator may not have an account on that system). Here are a few SQL statements that I use frequently.

Note: The database tables storing the user specific information have changed since JIRA 4.3. Be sure to select the correct SQL queries for your JIRA release.

For an article how to reset the JIRA password, see Recover lost JIRA password.
There is also an article on Useful Confluence database selects.

List all JIRA users, their full names and email addresses (JIRA Release 4.3 and newer)

select
    user_name, display_name, email_address
from
    cwd_user;

The user tables can actually store much more user attributes, such as the directory the user is a member of, whether the user is disabled etc.

To see all information, use

select
    *
from
    cwd_user;

List only JIRA administrators, their email address and full names (JIRA Release 4.3 and newer)

select
    user_name, display_name, email_address
from
    cwd_user, cwd_membership
where
    cwd_user.id = cwd_membership.child_id and
    cwd_membership.membership_type='GROUP_USER' 
    and cwd_membership.parent_name='jira-administrators';

List all JIRA users and their email address (JIRA Release 4.2 and before)

select
    userbase.username, propertystring.propertyvalue
from
    userbase, propertyentry, propertystring 
where 
    userbase.id=propertyentry.entity_id and 
    propertyentry.id=propertystring.id and 
    propertyentry.property_key='email';

List only JIRA administrators and their email address (JIRA Release 4.2 and before)

select
    userbase.username, propertystring.propertyvalue
from
    userbase, propertyentry, propertystring, membershipbase
where
    userbase.id=propertyentry.entity_id and
    propertyentry.id=propertystring.id and
    propertyentry.property_key='email' and 
    membershipbase.user_name=userbase.username and 
    membershipbase.group_name='jira-administrators';

List only JIRA administrators, their email address and full names (JIRA Release 4.2 and before)

select
    userbase.username, propertystringa.propertyvalue, propertystringb.propertyvalue
from
    userbase, propertyentry as propertyentrya, propertyentry as propertyentryb, propertystring as propertystringa, propertystring as propertystringb, membershipbase
where
    userbase.id=propertyentrya.entity_id and
    propertyentrya.id=propertystringa.id and
    propertyentrya.property_key='email' and 
	
    userbase.id=propertyentryb.entity_id and
    propertyentryb.id=propertystringb.id and
    propertyentryb.property_key='fullName' and 
   
    membershipbase.user_name=userbase.username and 
    membershipbase.group_name='jira-administrators';