KB : Script to bulk change canned response templates from global to personal and vice versa [Server/DC]

Purpose

This page describes the way how to change the canned response template type from global to personal and from global to personal from a database perspective.

This runbook applies to Jira Server/DC

Runbook:

Access Jira Database using the command line or multi-platform database tool (example: https://appfire.atlassian.net/wiki/spaces/SUPPORTSP/pages/156502991)

Execute the script to check the username to which you want to assign a global templates:

SELECT USER_KEY, LOWER_USER_NAME FROM PUBLIC.APP_USER;

Copy USER_KEY to '@user' values into the following script

SET @user = 'JIRAUSER10000';
SET @change_type = 'USER'; /* 'GLOBAL' or 'USER' allowed */

UPDATE
  AO_22BE8E_TEMPLATES
SET
  TEMPLATE_SCOPE = @change_type,
  OWNER_USER_KEY = CASE
    WHEN @change_type = 'USER' THEN @user
    WHEN @change_type = 'GLOBAL' THEN NULL
END;
COMMIT;

Execute the script to change the template properties.

Check if templates are now ‘USER’ type:

SELECT 
ID, 
MIGRATED_ID, 
MIGRATED_SCOPE, 
MIGRATED_USER, 
NAME, 
OWNER_USER_KEY, 
TEMPLATE_SCOPE, 
TEMPLATE_TEXT
FROM 
PUBLIC.AO_22BE8E_TEMPLATES;

You can also change properties of the single template to personal and then back to global using WHERE clause.