---
title: "Bulk Replacing BigPicture Scope Owner on Data Center"
canonical: "https://support.appfire.com/space/SUPPORT/3406856742/Bulk%20Replacing%20BigPicture%20Scope%20Owner%20on%20Data%20Center"
format: markdown
---
### When This Applies

A customer reports that scopes on their BigPicture boxes have stopped working because the user who was set as the **scope owner**’s Jira account has been disabled, i.e. because they left the company. They need to reassign scope ownership across many boxes to a new user (typically a technical/service account).

> 📝 This procedure applies to **BigPicture on Jira Data Center** only and involves direct database changes. It is not a UI-based bulk operation.

# Support summary

- BigPicture does **not** support bulk updating scope owners through the UI.
- If only a small number of Boxes is affected, advise the customer to update each Box manually.
- If many Boxes are affected, the supported workaround is a **phased PostgreSQL database procedure**.
- Because this requires mutation SQL, Support should treat it as a **high-risk guided operation**.
- If needed, involve L3/Commando, especially for large customers or high-priority production impact.

# When to use this runbook

- The customer is on **Data Center**.
- The scope type is **Own Scope**.
- The customer needs to identify Boxes owned by a specific user.
- The customer needs to replace that owner across many Boxes.
- Manual UI changes are not feasible.

# What Support should tell the customer

1. Create a **full database backup** before doing anything.
2. Perform the change in **three phases**: test Box, one real Box, then all remaining real Boxes.
3. **Disable the BigPicture app** before running mutation SQL.
4. **Re-enable the app** after each mutation step and verify behavior.
5. If anything unexpected happens, **stop immediately** and collect results before continuing.

> ⚠️ Do not recommend running the bulk update first. The safe path is: identify impacted Boxes, test on a new Box, validate on one existing Box, then run the bulk update.

# Decision guide for Support

| Situation | Recommended handling | Escalation need |
| --- | --- | --- |
| Only a few Boxes affected | Recommend manual updates in the UI | No |
| Many Boxes affected | Provide the phased DB procedure below | Recommended if agent is not comfortable with SQL |
| Customer is high tier, production impacted, or nervous about DB changes | Use the same procedure but involve L3/Commando early | Yes |

# Prerequisites

- Customer has **PostgreSQL database access**.
- Customer can disable and re-enable the BigPicture app.
- Customer can obtain the Jira **user key** for both the old and new account.
- Customer understands that this is a database-level operation performed at their own risk after backup.

# How to obtain the new owner user key

Ask the customer to log in as the new technical account and open:  
[https://<your_server>/rest/api/2/myself](#)

They should copy the value from the `key` field.

# Phase 0: Identify all impacted Boxes

Use this query to list all Boxes where a given user is the scope owner.

```sql
SELECT DISTINCT
    b."SURROGATE_ID"   AS box_id,
    b."NAME"           AS box_name,
    bs."ID"            AS scope_id,
    bse."EXTPPLATFORM_ID",
    bse."SCOPE_CONTEXT_USER" AS scope_owner
FROM "AO_0456E7_BOX" b
JOIN "AO_0456E7_BAT_SCOPE" bs
  ON b."SURROGATE_ID" = bs."BOX_REF_ID"
 AND bs."SCOPE_TYPE" = 'OWN'
JOIN "AO_0456E7_BAT_SCOPEDEF_EXTP" bse
  ON bse."SCOPE_CTX_BOX_ID" = bs."ID"
WHERE bse."SCOPE_CONTEXT_USER" = '2@USER_KEY'
ORDER BY b."SURROGATE_ID";
```

Instructions for Support:

- Tell the customer to replace `USER_KEY` with the former owner’s Jira user key.
- Expected output: Box ID, Box name, scope ID, platform ID, and current scope owner.
- Use the results to estimate impact and to select one Box for Phase 2 testing.

# Phase 1: Test on a newly created Box

Ask the customer to first create a separate test Box configured with **Own Scope**.

Suggested navigation to verify Box type configuration: **BigPicture → Administration → Classic Project → Tasks → Scope Definition**.

1. Create a new test Box using a type configured for Own Scope.
2. Record the Box ID.
3. Record the new owner’s Jira user key.
4. Disable the BigPicture app.
5. Run the SQL below.
6. Re-enable the app.
7. Verify that the scope owner changed successfully.

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

create or replace function replace_scope_owner_for_one_box(box_id text, new_owner text)
	returns void
	language plpgsql
as
$$
declare
    new_revision integer;
    scope_id integer;
    uuid text;
    event_string text;
    old_user text;
begin
    FOR scope_id, old_user IN
        SELECT bs."ID", bse."SCOPE_CONTEXT_USER" FROM "AO_0456E7_BOX" as b
            JOIN "AO_0456E7_BAT_SCOPE" bs ON b."SURROGATE_ID" = bs."BOX_REF_ID"
            JOIN "AO_0456E7_BAT_SCOPEDEF_EXTP" bse ON bse."SCOPE_CTX_BOX_ID" = bs."ID"
            WHERE b."SURROGATE_ID" = box_id
    LOOP
        raise notice 'Change the Scope owner for the box with id: % FROM % to %', box_id, old_user, new_owner;
        SELECT MAX("REVISION") + 1 into new_revision FROM "AO_0456E7_BAT_SDEF_EV_STORE"
            WHERE "SCOPE_CTX_BOX_ID" = scope_id
            AND "SUB_SCOPE_ID"= scope_id AND "EVENT_CATEGORY" = 'OWNSCOPE';
        raise notice 'Increment revision % to the new value %', new_revision - 1, new_revision;

        SELECT FORMAT('{"newScopeContextExtUserId":{"internal":"2@%s"},"boxId":{"internal":"%s"},"boxScopeId":{"internal":%s},' ||
                      '"extPlatformId":{"internal":2}}',
                      new_owner,
                      box_id,
                      scope_id)
        into event_string;

        INSERT INTO "AO_0456E7_BAT_SDEF_EV_STORE" ("CONTEXT_USER", "CREATE_DATE", "CREATE_DATE_EPOCH_MILLI", "EVENT_CATEGORY", "EVENT_DATA",
                                                   "EVENT_TYPE", "REVISION",
                                                   "SCOPE_CTX_BOX_ID", "SUB_SCOPE_ID", "UPDATE_DATE")
        VALUES(new_owner, now(),
               (FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000000))::bigint,
               'OWNSCOPE', event_string, 'OwnerUpdated', new_revision, scope_id, scope_id, now());
    END LOOP;
    Select uuid_generate_v1() into uuid;
    update "AO_0456E7_BAT_SDEF_ES_VER" 
    set "CATEGORY_VERSION_UUID" = uuid
    where "EVENT_CATEGORY" = 'OWNSCOPE';
end;
$$;

/* Replace with the real Box ID and new owner user key */
select replace_scope_owner_for_one_box('BOX_ID_TO_CHANGE_THE_SCOPE_OWNER_FOR', 'NEW_USER_KEY');
```

> ℹ️ If the customer wants the exact execution sequence, tell them to run the function creation first, then execute the final `select replace_scope_owner_for_one_box(...)` statement.

# Phase 2: Validate on one real Box

Once the test Box succeeds, repeat the same process for **one existing production Box** from the impacted list.

1. Pick one affected real Box from the identification query results.
2. Disable the BigPicture app.
3. Run the same single-Box SQL again, using the real Box ID.
4. Re-enable the app.
5. Confirm the scope owner was updated and the Box behaves correctly.

Use the same query as in Phase 1:

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

create or replace function replace_scope_owner_for_one_box(box_id text, new_owner text)
	returns void
	language plpgsql
as
$$
declare
    new_revision integer;
    scope_id integer;
    uuid text;
    event_string text;
    old_user text;
begin
    FOR scope_id, old_user IN
        SELECT bs."ID", bse."SCOPE_CONTEXT_USER" FROM "AO_0456E7_BOX" as b
            JOIN "AO_0456E7_BAT_SCOPE" bs ON b."SURROGATE_ID" = bs."BOX_REF_ID"
            JOIN "AO_0456E7_BAT_SCOPEDEF_EXTP" bse ON bse."SCOPE_CTX_BOX_ID" = bs."ID"
            WHERE b."SURROGATE_ID" = box_id
    LOOP
        raise notice 'Change the Scope owner for the box with id: % FROM % to %', box_id, old_user, new_owner;
        SELECT MAX("REVISION") + 1 into new_revision FROM "AO_0456E7_BAT_SDEF_EV_STORE"
            WHERE "SCOPE_CTX_BOX_ID" = scope_id
            AND "SUB_SCOPE_ID"= scope_id AND "EVENT_CATEGORY" = 'OWNSCOPE';
        raise notice 'Increment revision % to the new value %', new_revision - 1, new_revision;

        SELECT FORMAT('{"newScopeContextExtUserId":{"internal":"2@%s"},"boxId":{"internal":"%s"},"boxScopeId":{"internal":%s},' ||
                      '"extPlatformId":{"internal":2}}',
                      new_owner,
                      box_id,
                      scope_id)
        into event_string;

        INSERT INTO "AO_0456E7_BAT_SDEF_EV_STORE" ("CONTEXT_USER", "CREATE_DATE", "CREATE_DATE_EPOCH_MILLI", "EVENT_CATEGORY", "EVENT_DATA",
                                                   "EVENT_TYPE", "REVISION",
                                                   "SCOPE_CTX_BOX_ID", "SUB_SCOPE_ID", "UPDATE_DATE")
        VALUES(new_owner, now(),
               (FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000000))::bigint,
               'OWNSCOPE', event_string, 'OwnerUpdated', new_revision, scope_id, scope_id, now());
    END LOOP;
    Select uuid_generate_v1() into uuid;
    update "AO_0456E7_BAT_SDEF_ES_VER" 
    set "CATEGORY_VERSION_UUID" = uuid
    where "EVENT_CATEGORY" = 'OWNSCOPE';
end;
$$;

select replace_scope_owner_for_one_box('BOX_ID_TO_CHANGE_THE_SCOPE_OWNER_FOR', 'NEW_USER_KEY');
```

# Phase 3: Bulk update all remaining Boxes

Only proceed after the first two phases have been validated.

1. Disable the BigPicture app.
2. Run the bulk function below.
3. Execute the final replacement statement with the old and new user keys.
4. Re-enable the BigPicture app.
5. Ask the customer to validate a sample of updated Boxes.

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

create or replace function replace_scope_owner(old_user text, new_user text)
    returns void
    language plpgsql
as
$$
declare
    box_id       text;
    scope_id     integer;
    new_revision integer;
    uuid         text;
    event_string text;
begin
    FOR box_id IN
       Select box."SURROGATE_ID"
       from "AO_0456E7_BAT_SCOPEDEF_EXTP" sdef
               join "AO_0456E7_BAT_SCOPE" scope on scope."ID" = sdef."SCOPE_CTX_BOX_ID"
               join "AO_0456E7_BOX" box on box."SURROGATE_ID" = scope."BOX_REF_ID"
       where sdef."SCOPE_CONTEXT_USER" = concat('2@', old_user)
    LOOP
        SELECT "ID" into scope_id FROM "AO_0456E7_BAT_SCOPE" WHERE "BOX_REF_ID" = box_id;
        raise notice 'bum %', old_user;
        SELECT MAX("REVISION") + 1 into new_revision FROM "AO_0456E7_BAT_SDEF_EV_STORE"
            WHERE "SCOPE_CTX_BOX_ID" = scope_id and "SUB_SCOPE_ID" = scope_id;
        Select FORMAT(
                     '{"newScopeContextExtUserId": {"internal":"2@%s"},"boxId":{"internal":"%s"},"boxScopeId":{"internal":"%s"} ,' ||
                     '"extPlatformId":{"internal":2}}',
                     new_user,
                     box_id,
                     scope_id
               )
        into event_string;
        INSERT INTO "AO_0456E7_BAT_SDEF_EV_STORE" ("CONTEXT_USER", "CREATE_DATE", "CREATE_DATE_EPOCH_MILLI", "EVENT_CATEGORY", "EVENT_DATA",
                                                   "EVENT_TYPE", "REVISION",
                                                   "SCOPE_CTX_BOX_ID", "SUB_SCOPE_ID", "UPDATE_DATE")
        values (new_user, now(),
                (FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000000))::bigint,
                'OWNSCOPE', event_string, 'OwnerUpdated', new_revision, scope_id, scope_id, now());
    END LOOP;
    Select uuid_generate_v1() into uuid;
    update "AO_0456E7_BAT_SDEF_ES_VER" 
    set "CATEGORY_VERSION_UUID" = uuid
    where "EVENT_CATEGORY" = 'OWNSCOPE';
end;
$$;

/* Replace with real user keys */
select replace_scope_owner('OLD_USER_KEY', 'NEW_USER_KEY');
```