Pre-requisites

You will need to have Python 2.7 or later installed on the workstation you will use with this solution.

Purpose

Some organizations wish to disable all email preferences for users manually through the use of SQL queries without disabling the email server in their Confluence instance. This means that users must manually alter their email preferences in order to receive mail from Confluence.  Note: This solution is only effective for users that exist when the instance is altered. New users that are added after the implementation of this solution will not be effected by this change and will have the default user email preferences checked. This solution is meant as a one-time only solution and carries the risk of added complexity in your instance.  It is far easier to simply disable the email server, but this solution is provided to those that wish to ensure that your user's email profiles will have no boxes checked by default:

Solution

Step One: Identify your users

In order to gather the users to be modified, you will need to run the following query against your database and then export the output file as a comma separated values (CSV) file. You should name this file: input.csv.

Query One

SELECT
um.user_key
FROM
user_mapping um
WHERE
um.user_key NOT IN
(
SELECT
concat('USERPROPS-', um.user_key) AS userKey
FROM
os_propertyentry
WHERE
entity_key IN ('confluence.prefs.notify.for.my.own.actions',
'confluence.prefs.watch.my.own.content',
'confluence.prefs.email.show.diff',
'confluence.prefs.email.mimetype',
'confluence.prefs.notify.on.new.followers',
'confluence.prefs.daily.summary.receive.updates.set',
'confluence.prefs.daily.summary.receive.updates'));

This will output a list of all userKey values for every user present in Confluence when run.

Save the file as input.csv as previously stated.

Create a new text file

The following file should be created in your text editor of choice:

INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.notify.for.my.own.actions', 1, false, 0, '', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.watch.my.own.content', 1, false, 0, '', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.email.show.diff', 1, false, 0 , '', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.email.mimetype', 5, false, 0, 'text/html', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.notify.on.new.followers', 1, false, 0, '', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.daily.summary.receive.updates.set', 1, true, 0, '', '', 0, 0, NULL);
INSERT INTO os_propertyentry (entity_name, entity_id, entity_key, key_type, boolean_val, double_val, string_val, text_val, long_val, int_val, date_val) VALUES ('USERPROPS-{0}', 0, 'confluence.prefs.daily.summary.receive.updates', 1, false, 0, '', '', 0, 0, NULL);

Save this file as template.sql locally.

Gather Support SQL Generator, a python script, that will take a CSV file and an SQL template as its input. For each row in the CSV file, the columns are swapped into the template. If you've ever needed to write SQL for large scale fixes, this excellent tool will help you automate the SQL queries  that you will need to write.

Steps to utilize the SQL Generator script:

1. Download the files in the SQL Generator zip file.
2. Extract this into a folder of your choice.
3. Copy your files input.csv and template.sql into this extracted folder
4. Ensure that you have Python 2.7 or later installed on the workstation you will perform the query on
5. Run the command python generate.py and then save the file output.sql once this has finished.
6. This will create several rows to modify in the OS_PROPERTYENTRY table for each user in the system.
7. Using your Database tools, run the file output.sql against the database to insert the rows for your users.
    Be patient, this may take some time to complete at scale. 
8. Start your Confluence instance
9. Spot check user profiles to ensure that that options are unchecked as desired.