Having two users with the same email address can cause email problems

Say a user has accidentally registered twice, first as 'jbloggs', second as 'joe', which is the account they use, in all the right groups. Both accounts will have the same email address. This is usually not a problem, except when using a POP/IMAP service to create issues automatically. The mail handler uses the email address to find out the user. If there is more than one user with the same email address, it will pick the first match ('jbloggs') which may not be the right one - eg. it may not have permission to create issues. In this case, JIRA places the email in the error queue.

To avoid this, we should either enforce email uniqueness, or print a warning when an account is created using an existing email address.

Workaround

We can use the following SQL query to identify the users with duplicated e-mail addresses to know which users to edit.

with users as (
	select lower_user_name, lower_email_address 
	from cwd_user CU
	join cwd_directory CD on CU.directory_id = CD.id and CD.active = 1
	group by lower_user_name, lower_email_address),
duplicated as (
	select lower_email_address from users
	group by lower_email_address 
	having count(lower_email_address) > 1)
select U.lower_user_name, U.lower_email_address, CD.directory_name , coalesce (UA.attribute_name, 'login.count') as attribute_name, coalesce (UA.attribute_value,'0') as attribute_value
from duplicated D
join cwd_user U on D.lower_email_address = U.lower_email_address 
join cwd_directory CD on U.directory_id = CD.id
left join cwd_user_attributes UA on UA.user_id = U.id and UA.attribute_name = 'login.count'
order by u.lower_email_address, attribute_value desc;

Please note if you need to edit a user not in the Jira Internal Directory, you'll most likely need to change the e-mail address in the LDAP server directly, unless you use a Delegated directory without the Update User attributes on Login setting.

You can use the Update User REST API to speed up the process.