Update REDCap user's email from MySQL

Information Required:

• Current WashU Key
• Previous WashU Key
• REDCap MySQL Server
• REDCAP MySQL database

Step 1) backup tables that will be changed, needed in case tables need to be reset (see rollback steps)

CREATE TABLE redcap_user_rights_bkp LIKE redcap_user_rights; 
INSERT INTO redcap_user_rights_bkp SELECT * FROM redcap_user_rights;

CREATE TABLE redcap_data_access_groups_users_bkp LIKE redcap_data_access_groups_users; 
INSERT INTO redcap_data_access_groups_users_bkp SELECT * FROM redcap_data_access_groups_users;

CREATE TABLE redcap_reports_access_users_bkp LIKE redcap_reports_access_users; 
INSERT INTO redcap_reports_access_users_bkp SELECT * FROM redcap_reports_access_users;

Step 2) apply REDCap user changes to database

-- get table counts for new and previous user
use \<REDCAP MySQL database\>
select 'current','redcap_user_rights',count(*) from redcap_user_rights where username ='<Previous WashU Key>'
union
select 'current','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='<Previous WashU Key>'
union
select 'current','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='<Previous WashU Key>'
union
select 'new','redcap_user_rights',count(*) from redcap_user_rights where username ='<Current WashU Key>'
union
select 'new','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='<Current WashU Key>'
union
select 'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='<Current WashU Key>';

-- make user changes in database
update redcap_user_rights set username = '<Current WashU Key>' where username ='<Previous WashU Key>';
update redcap_data_access_groups_users set username = '<Current WashU Key>' where username ='<Previous WashU Key>';
update redcap_reports_access_users set username = '<Current WashU Key>' where username = '<Previous WashU Key>';

-- validate counts have inverted for new and previous user
select 'current','redcap_user_rights',count(*) from redcap_user_rights where username ='<Previous WashU Key>'
union
select 'current','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='<Previous WashU Key>'
union
select 'current','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='<Previous WashU Key>'
union
select 'new','redcap_user_rights',count(*) from redcap_user_rights where username ='<Current WashU Key>'
union
select 'new','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='<Current WashU Key>'
union
select 'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='<Current WashU Key>';

rollback steps) used if needed to reset the user tables

RENAME TABLE redcap_user_rights TO redcap_user_rights_drop;
RENAME TABLE redcap_user_rights_bkp TO redcap_user_rights;
DROP TABLE redcap_user_rights_drop;

RENAME TABLE redcap_data_access_groups_users TO redcap_data_access_groups_users_drop;
RENAME TABLE redcap_data_access_groups_users_bkp TO redcap_data_access_groups_users;
DROP TABLE redcap_data_access_groups_users_drop;

RENAME TABLE redcap_reports_access_users TO redcap_reports_access_users_drop;
RENAME TABLE redcap_reports_access_users_bkp TO redcap_reports_access_users;
DROP TABLE redcap_reports_access_users_drop;

example execution

[warren.thomas@compute1-exec-390i: ~] $ mysql -h 10.24.96.138 -u i2adminv8 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3549099
Server version: 8.0.40-azure Source distribution

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| redcap_appp60m1    |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use redcap_appp60m1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE redcap_user_rights_bkp LIKE redcap_user_rights;
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO redcap_user_rights_bkp SELECT * FROM redcap_user_rights;
Query OK, 65742 rows affected (3.56 sec)
Records: 65742  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE redcap_data_access_groups_users_bkp LIKE redcap_data_access_groups_users;
ers_bkp SELECT * FROM redcap_data_access_groups_users;Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO redcap_data_access_groups_users_bkp SELECT * FROM redcap_data_access_groups_users;
Query OK, 2442 rows affected (0.21 sec)
Records: 2442  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE redcap_reports_access_users_bkp LIKE redcap_reports_access_users;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO redcap_reports_access_users_bkp SELECT * FROM redcap_reports_access_users;
Query OK, 6974 rows affected (0.49 sec)
Records: 6974  Duplicates: 0  Warnings: 0

mysql> select 'current','redcap_user_rights',count(*) from redcap_user_rights where username ='------@wustl.edu'
'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu';    -> union
    -> select 'current','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='------@wustl.edu'
    -> union
    -> select 'current','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_data_access_groups_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu';
+---------+---------------------------------+----------+
| current | redcap_user_rights              | count(*) |
+---------+---------------------------------+----------+
| current | redcap_user_rights              |       31 |
| current | redcap_data_access_groups_users |        0 |
| current | redcap_reports_access_users     |        1 |
| new     | redcap_user_rights              |        0 |
| new     | redcap_reports_access_users     |        0 |
| new     | redcap_data_access_groups_users |        0 |
+---------+---------------------------------+----------+
5 rows in set (0.02 sec)

mysql> update redcap_user_rights set username = '------@wustl.edu' where username ='------@wustl.edu';
Query OK, 31 rows affected (0.09 sec)
Rows matched: 31  Changed: 31  Warnings: 0

mysql> update redcap_data_access_groups_users set username = '------@wustl.edu' where username ='------@wustl.edu';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update redcap_reports_access_users set username = '------@wustl.edu' where username = '------@wustl.edu';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update redcap_user_rights set username = '------@wustl.edu' where username ='------@wustl.edu';
Query OK, 31 rows affected (0.09 sec)
Rows matched: 31  Changed: 31  Warnings: 0

mysql> update redcap_data_access_groups_users set username = '------@wustl.edu' where username ='------@wustl.edu';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update redcap_reports_access_users set username = '------@wustl.edu' where username = '------@wustl.edu';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select 'current','redcap_user_rights',count(*) from redcap_user_rights where username ='------@wustl.edu'
    -> union
    -> select 'current','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='------@wustl.edu'
    -> union
    -> select 'current','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_user_rights',count(*) from redcap_user_rights where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_data_access_groups_users',count(*) from redcap_data_access_groups_users where username ='------@wustl.edu'
    -> union
    -> select 'new','redcap_reports_access_users',count(*) from redcap_reports_access_users where username ='------@wustl.edu';
+---------+---------------------------------+----------+
| current | redcap_user_rights              | count(*) |
+---------+---------------------------------+----------+
| current | redcap_user_rights              |        0 |
| current | redcap_data_access_groups_users |        0 |
| current | redcap_reports_access_users     |        0 |
| new     | redcap_user_rights              |       31 |
| new     | redcap_data_access_groups_users |        0 |
| new     | redcap_reports_access_users     |        1 |
+---------+---------------------------------+----------+
6 rows in set (0.02 sec)