When a right to be forgotten request is received you can use the stored procedures below to obfuscate the PI from the Pursuit database in the Legacy App Data Explorer.
Pursuit
- Connect to the PURSUIT database on the vistrylegacy.database.windows.net SQL Server using SSMS.
- Navigate to stored procedures and modify the SP_PURSUIT_OBFUSCATE_PURCHASER_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
- This will generate the script as a new query. Follow the instructions to identify the company ID of the purchaser and execute the stored procedure to obfuscate their personal information. See instructions from the script below.
--- ===========================================================================================================
-- Follow these steps to obfuscate the PI of a Purchaser who has file a right to be forgotten request.
-- 1) Identify the Company ID for the purchaser.
-- Use the view below to find the Company ID.
/*
SELECT [Purchaser ID] AS COMPANY_ID
,*
FROM dbo.PBI_Purchaser
WHERE [Purchaser Name] LIKE '%Enter Search Name Here%'
*/
-- 2) Execute this stored procedure providng the Company ID you found in Step one as the @COMPANY_ID parameter.
/*
EXEC dbo.SP_PURSUIT_OBFUSCATE_PURCHASER_PI_RIGHT_TO_BE_FORGOTTEN @COMPANY_ID = 'Enter Company ID here'
*/
--3) Check that all the PI has been replaced by "-----" by running these queries here.
/*
SELECT * FROM dbo.PBI_Purchaser WHERE [Purchaser ID] = 'Enter Company ID Here'
SELECT * FROM dbo.PBI_Purchaser_Contact WHERE [Purchaser ID] = 'Enter Company ID Here'
*/
--- ===========================================================================================================
Connections
No pre-written script is currently available.
SERUM
No pre-written script is currently available.
Epic
No pre-written script is currently available.
Focus
- Connect to the FOCUS database on the vistrylegacy.database.windows.net SQL Server using SSMS.
- Navigate to stored procedures and modify the SP_FOCUS_OBFUSCATE_CUSTOMER_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
- This will generate the script as a new query. Follow the instructions to identify the Customer ID of the customer and execute the stored procedure to obfuscate their personal information. See instructions from the script below.
--- ===========================================================================================================
-- Follow these steps to obfuscate the PI of a Customer who has filed a right to be forgotten request.
-- 1) Identify the Customer ID for the Customer.
-- Use the view below to find the Customer ID.
/*
SELECT CustomerId
,Name
FROM dbo.Customer AS CUS
LEFT OUTER JOIN dbo.Entity AS CUS_ENT
ON CUS.EntityID = CUS_ENT.EntityID
WHERE [Name] LIKE '%Enter Search Name Here%'
*/
-- 2) Execute this stored procedure providng the Company ID you found in Step one as the @CUSTOMER_ID parameter.
/*
EXEC dbo.SP_FOCUS_OBFUSCATE_CUSTOMER_PI_RIGHT_TO_BE_FORGOTTEN @CUSTOMER_ID = 'Enter Customer ID here'
*/
--3) Check that all the PI has been replaced by "-----" by running these queries here.
/*
SELECT CUS.AddressAs
,CUS_ENT.Name
,CUS_CON.TitleID --TitleID 20 is "Unknown"
,CUS_CON.FirstName
,CUS_CON.Surname
,CUS_CON.WorkTelephoneNumber
,CUS_CON.HomeTelephoneNumber
,CUS_CON.MobileNumber
,CUS_CON.FaxNumber
,CUS_CON.EMailHome
,CUS_CON.EmailWork
,CUS_CON.Other
FROM Customer AS CUS
LEFT OUTER JOIN dbo.Entity AS CUS_ENT
ON CUS.EntityID = CUS_ENT.EntityID
LEFT OUTER JOIN dbo.Contact AS CUS_CON
ON CUS.EntityID = CUS_CON.EntityID
WHERE CUS.CustomerID = 'Enter Customer ID Here'
*/
--- ===========================================================================================================
Contact Builder (Millgate)
- Connect to the CONTACT_BUILDER_MILLGATE database on the vistrylegacy.database.windows.net SQL Server using SSMS.
- Navigate to stored procedures and modify the SP_OBFUSCATE_PURCHASER_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
- This will generate the script as a new query. Follow the instructions to identify the Contact ID of the contact and execute the stored procedure to obfuscate their personal information. See instructions from the script below.
--- ==========================================================================================================
-- Follow these steps to obfuscate the PI of a Customer who has filed a right to be forgotten request.
-- 1) Identify the Contact ID for the Contact.
-- Use the SQL below to find the Contact ID.
/*
SELECT CON.id AS [Contact Id]
,COALESCE(CON.first_name + ' ' ,'')
+ COALESCE(CON.middle_names + ' ','')
+ COALESCE(CON.last_name,'') AS [Contact Name]
FROM [mysql-millgate].[contacts] As CON
WHERE COALESCE(CON.first_name + ' ' ,'')
+ COALESCE(CON.middle_names + ' ','')
+ COALESCE(CON.last_name,'') LIKE '%Insert Name Here%'
*/
-- 2) Execute this stored procedure providng the Contact ID you found in Step one as the @CONTACT_ID parameter.
/*
EXEC dbo.[dbo.SP_OBFUSCATE_PURCHASER_PI_RIGHT_TO_BE_FORGOTTEN] @CONTACT_ID = 'Contact ID Here'
*/
--3) Check that all the PI has been replaced by "-----" by running this SQL.
/*
SELECT CON.[title]
,CON.[first_name]
,CON.[middle_names]
,CON.[last_name]
,CON.[address1]
,CON.[address2]
,CON.[city]
,CON.[county]
,CON.[state]
,CON.[postcode]
,CON.[country]
,CON.[email]
,CON.[mobile_phone]
,CON.[home_phone]
,CON.[work_phone]
,CON.[notes]
FROM [mysql-millgate].[contacts] AS CON
WHERE Id = 'Enter Contact ID Here'
*/
--- ===========================================================================================================