Azure Databases: Changing user wustl key in database

• In Redcap, users are assigned to projects using the projectid together with their distinct wustl key.
• However, sometimes users might undergo a name change thus resulting in a wustl key change.
  • This is also creates multiple projects been assigned to multiple distinct wustl keys associated to that single user.
• If a user has two wustl key, we may occasionally receive a request to replace a user's old wustl key with the new wustl key and assign them existing projects.
• The following SQL statements allows you to perform the change in the redcap database.

Wustl key update SQL statements

• Note that redcap_appd87m1 is the name of the database, change it to match the name of the database in question.
• The script below validates source table count before backing up.

Select count(*) from redcap_appd87m1.redcap_user_rights;

• The script below backs up source table

Create table redcap_appd87m1.redcap_user_rights_bck
as Select * from redcap_appd87m1.redcap_user_rights;

• The script below validates the backup table count, make sure it matches source table count.

Select * from redcap_appd87m1.redcap_user_rights_bck;

• The script below provides the number projects associated to the old wustl key.
• Enter the desired wustl key name in between %%.

Select count(*) from redcap_appd87m1.redcap_user_rights where username like '%%'; 

• The script below provides the number projects associated to the new wustl key.wustl key

Select count(*) from redcap_appd87m1.redcap_user_rights where username like '%%';

• The script below Checks for overlapping projects (verifies to ensure that old and new wustl key do not share projects).

Select * from redcap_appd87m1.redcap_user_rights where username like '%new_wustl_key%' and username like '%old_wustl_key%';

• The script below updates the old key wustl with the new key

Update redcap_appd87m1.redcap_user_rights set username = 'new_wustl_key' where username = 'old_wustl_key';

• In case the delete fails because of primary/foreign key constraints related to project_id, you will have to delete records associated to one of the wustl keys.
• Use the script below to delete the records associated to one of the wustl keys

delete from redcap_appd87m1.redcap_user_rights where username = 'new_wustl_key';

• The scripts below ensures that no downtime is needed in case of failure.
• If the update statement fails, you will first need to drop data from the source table.
• Use the below to delete the records as needed.

Delete from redcap_appd87m1.redcap_user_rights;

• Next, use the script ne;ow to insert data from the backup table to the source table.

Insert into redcap_appd87m1.redcap_user_rights
Select * from redcap_appd87m1.redcap_user_rights_bck

• Use the script below to validate the record counts and ensure that the data matches the pre-updated source table data.

Select count(*) from redcap_appd87m1.redcap_user_rights;

Select count(*) from redcap_appd87m1.redcap_user_rights where username like '%new_wustl_key%';

Select count(*) from redcap_appd87m1.redcap_user_rights where username like '%old_wustl_key%';

• The script below drops the backup table when the task completed or when the back up table is not longer needed.

Drop table redcap_appd87m1.redcap_user_rights_bck;