Jira boards owned by invalid users
This insight checks if any of your Jira boards are owned by inactive or deleted users.
What's the problem?
Boards owned by inactive or deleted users won't block your migration, but they might be set to private in Jira Cloud, without easy access to update them later on. To avoid these issues, we recommend that you clean up your board owners before you migrate.
What’s the recommendation?
To avoid issues with boards set to private in Jira Cloud:
Review affected boards
Change board owners to active users
Update board owners to active users
Follow these steps to update board owners.
Review affected boards
When viewing this insight from Portfolio insights, copy the SQL query (also pasted below). The query returns boards owned by invalid or inactive users, together with:
Board name
Board owner username, email address, display name, and user key
SELECT rv.NAME AS "Board name",
cwu.lower_user_name AS "Board inactive owner username",
cwu.lower_email_address AS "Board inactive owner email address",
cwu.display_name AS "Board inactive owner display name",
au.user_key AS "Board inactive owner user key"
FROM cwd_user cwu
INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
WHERE cwu.active = 0
ORDER BY rv.ID;Fix: Update board owners to active users directly in the database
When boards are created, their owners are saved in a different database table than administrators, that's why just changing board admins in Jira UI might not be enough.
Use the following SQL queries to update the board owners directly in the database.
Make sure to choose an active Jira user to replace the owner.
Updating board owned by inactive users
Choose the SQL query depending on your database.
PostgreSQL
UPDATE "AO_60DB71_RAPIDVIEW"
SET "OWNER_USER_NAME" = '<user name that will be the new owner of these boards>'
WHERE "ID" IN ( SELECT rv."ID"
FROM cwd_user cwu
INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
JOIN "AO_60DB71_RAPIDVIEW" rv ON (rv."OWNER_USER_NAME" = cwu.lower_user_name OR rv."OWNER_USER_NAME" = au.user_key)
WHERE cwu.active = 0 );Oracle, Microsoft SQL Server
UPDATE AO_60DB71_RAPIDVIEW
SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
WHERE ID IN ( SELECT rv.ID
FROM cwd_user cwu
INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
WHERE cwu.active = 0 );MySQL
SET SQL_SAFE_UPDATES = 0;
UPDATE AO_60DB71_RAPIDVIEW
SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
WHERE ID IN ( SELECT ID
FROM ( SELECT rv.ID
FROM cwd_user cwu
INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
WHERE cwu.active = 0 ) AS t );
SET SQL_SAFE_UPDATES = 1;Updating board owned by deleted users
Choose the SQL query depending on your database.
PostgreSQL
UPDATE "AO_60DB71_RAPIDVIEW"
SET "OWNER_USER_NAME" = '<user name that will be the new owner of these boards>'
WHERE "ID" IN ( SELECT rv."ID"
FROM "AO_60DB71_RAPIDVIEW" rv
JOIN app_user u ON (rv."OWNER_USER_NAME" = u.user_key)
LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
WHERE NOT EXISTS ( SELECT *
FROM cwd_user
WHERE lower_user_name = u.lower_user_name ));Oracle, Microsoft SQL Server
UPDATE AO_60DB71_RAPIDVIEW
SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
WHERE ID IN ( SELECT rv.ID
FROM AO_60DB71_RAPIDVIEW rv
JOIN app_user u ON (rv.OWNER_USER_NAME = u.user_key)
LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
WHERE NOT EXISTS ( SELECT *
FROM cwd_user
WHERE lower_user_name = u.lower_user_name ));MySQL
UPDATE AO_60DB71_RAPIDVIEW rv
JOIN app_user u ON (rv.OWNER_USER_NAME = u.user_key)
LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
SET OWNER_USER_NAME = '<user name that will be the new owner of these boards>'
WHERE NOT EXISTS ( SELECT *
FROM cwd_user
WHERE lower_user_name = u.lower_user_name );
Was this helpful?