Reassign Report ownership - deleted user case (DC only)

Due to major differences across various Database Management Systems (Postgres, MySQL, Oracle DB etc.), make sure you do not run the guide below blindly, but adapt it for your infrastructure setup!

- 1.1 When to use
- 1.2 Do first
2 Identify the Owner table name
3 Get the user keys (exact strings)
4 Backup at least the affected rows
5 Reassign the ownership in one transaction
6 Quick verification
- 6.1 Notes
7 Done
8 Pitfalls & DBMS differences (read this)

When to use

Original report owner user was deleted, and you need to transfer ownership and references to an active user.

Do first

Back up the database (standard DB backup).
Run during a maintenance window.
Ensure the new owner user exists and is active in Jira.

Identify the Owner table name

Report Builder data lives in a table named like AO_******_REPORTV3 (the middle hash varies). Typically it’s AO_8A209A_REPORTV3

Owner meaning: The UI uses CREATED_BY as owner by default. Updating UPDATED_BY and PERMISSIONS keeps data consistent.

Find the table quickly (any DB):

Open your DB’s table list and search for REPORTV3, or
Run a simple metadata/table search (use your DB UI).

Use the exact table name you find in all commands below (we’ll refer to it as AO_XXXXXX_REPORTV3).

Get the user keys (exact strings)

You need two strings:

JIRAUSER<new_user_id> → new owner (e.g., JIRAUSER10012)
JIRAUSER<removed_user_id → deleted user (e.g., JIRAUSER10345)

Get the new owner key by login (example):

SELECT user_key FROM app_user WHERE lower_user_name = 'jane.doe';

If the removed user isn’t in app_user, read it from existing rows:

SELECT DISTINCT CREATED_BY FROM AO_XXXXXX_REPORTV3;
SELECT DISTINCT UPDATED_BY FROM AO_XXXXXX_REPORTV3;
SELECT * FROM AO_XXXXXX_REPORTV3 WHERE PERMISSIONS LIKE '%JIRAUSER<removed_user_id>%';

Note: If there are not that many report authors exist, and most of them were created by single user (business should know it), probably it would be enough just to see who of users has the most reports created.

Pick the exact string you see (copy/paste it verbatim).

Tip: Treat the user key as an opaque string. Don’t alter it, paste exactly what you found.

Backup at least the affected rows

Create a quick snapshot of rows you’ll touch:

-- Creates a copy table with just the impacted rows
-- (If your DB doesn’t support CREATE TABLE AS, just export the SELECT result to CSV.)
CREATE TABLE rbv3_backup_owners AS
SELECT *
FROM AO_XXXXXX_REPORTV3
WHERE CREATED_BY = 'JIRAUSER<removed_user_id'
   OR UPDATED_BY = 'JIRAUSER<removed_user_id'
   OR PERMISSIONS LIKE '%JIRAUSER<removed_user_id%';

Note: We recommend making sure you have full DB backup before any write operations into DB.

Reassign the ownership in one transaction

You may run this step as a set of independent queries too, the transaction is just more consistent

Replace AO_XXXXXX_REPORTV3, JIRAUSER<removed_user_id, and JIRAUSER<new_user_id> before running.

BEGIN;

-- 1) Ownership (creator)
UPDATE AO_XXXXXX_REPORTV3
SET CREATED_BY = 'JIRAUSER<new_user_id>'
WHERE CREATED_BY = 'JIRAUSER<removed_user_id';

-- 2) Last updater (not strictly “ownership”, but keeps metadata clean)
UPDATE AO_XXXXXX_REPORTV3
SET UPDATED_BY = 'JIRAUSER<new_user_id>'
WHERE UPDATED_BY = 'JIRAUSER<removed_user_id';

-- 3) Permissions (simple string replace)
UPDATE AO_XXXXXX_REPORTV3
SET PERMISSIONS = REPLACE(PERMISSIONS, 'JIRAUSER<removed_user_id', 'JIRAUSER<new_user_id>')
WHERE PERMISSIONS LIKE '%JIRAUSER<removed_user_id%';

COMMIT;

If your DB complains about quoting, use your engine’s style (no quotes / backticks / brackets). The logic stays the same.

Quick verification

-- Should return 0:
SELECT COUNT(*) AS remaining_refs
FROM AO_XXXXXX_REPORTV3
WHERE CREATED_BY = 'JIRAUSER<removed_user_id'
   OR UPDATED_BY = 'JIRAUSER<removed_user_id'
   OR PERMISSIONS LIKE '%JIRAUSER<removed_user_id%';

-- Spot-check a few rows now pointing to the new key:
SELECT ID, NAME, CREATED_BY, UPDATED_BY, PERMISSIONS
FROM AO_XXXXXX_REPORTV3
WHERE CREATED_BY = 'JIRAUSER<new_user_id>'
   OR UPDATED_BY = 'JIRAUSER<new_user_id>'
   OR PERMISSIONS LIKE '%JIRAUSER<new_user_id>%'
FETCH FIRST 50 ROWS ONLY;  -- or LIMIT 50 / TOP 50 depending on your DB

Open Jira → Report Builder and confirm the reports show the new owner.

Notes

Caching: If the UI lags, give it a few minutes. If needed, restart Jira or disable/enable the app.
Clusters: In DC clusters, make sure all nodes pick up the change (apply during maintenance; a rolling restart is fine).
Audit: Keep the backup table (rbv3_backup_owners) and the exact SQL you ran.

Done

Congratulations! This transfers ownership and cleans up references without diving into special patterns or edge cases.

Pitfalls & DBMS differences (read this)

Keep the process simple, but remember for these common cross-database quirks:

Identifier quoting & schema names
- PostgreSQL: table/column names are case-sensitive when quoted. Use exact case for AO tables (often uppercase), e.g. "public"."AO_XXXXXX_REPORTV3".
- SQL Server: [dbo].[AO_XXXXXX_REPORTV3] with square brackets works everywhere.
- MySQL/MariaDB: backticks (`AO_XXXXXX_REPORTV3`) or no quotes typically work; case sensitivity depends on OS/FS.
Transactions & autocommit
- Use an explicit transaction to keep it safe.
- PostgreSQL: BEGIN; … COMMIT;
- MySQL: START TRANSACTION; … COMMIT; (autocommit may be ON by default)
- SQL Server: BEGIN TRAN; … COMMIT TRAN;
- If anything looks off before committing, run ROLLBACK / ROLLBACK TRAN.
String replace function
- The REPLACE() function exists in PostgreSQL, MySQL, and SQL Server with the same argument order (REPLACE(column, 'from', 'to')).
- Don’t confuse MySQL’s REPLACE INTO (an INSERT/UPSERT) with the REPLACE() function used in SET.
Case sensitivity in searches
- LIKE is case-sensitive in PostgreSQL (use ILIKE if you need case-insensitive).
- MySQL and SQL Server case behavior depends on collation; defaults are typically case-insensitive.
- Since JIRAUSER… keys are uppercase, the examples usually work as-is.
MySQL “safe updates” mode
- If you see an error about safe updates, temporarily run SET SQL_SAFE_UPDATES = 0; (or include a limiting condition). Turn it back on if your policy requires.
Large text columns
- PERMISSIONS may be a large text type (TEXT/CLOB/NVARCHAR(MAX)). Some DB tools truncate previews; that’s a UI artifact. The REPLACE() update still works.
Clusters & caching (Jira DC)
- Apply during a maintenance window. After commit, allow a few minutes for the UI; if needed, restart Jira or disable/enable the app so all nodes refresh.
Quoting of string values
- Always wrap user keys in single quotes: 'JIRAUSER12345'. Don’t strip or alter the key—paste exactly what you found.
Row count sanity
- After each UPDATE, quickly check affected rows (or run the verification queries). If the numbers are unexpectedly high or zero, rollback and re-check your keys/table name.