Right to be Forgotten - Legacy App Data Explorer

When a right to be forgotten request is received you can use the stored procedures below to obfuscate the PI from the VistryLegacy SQL Azure Databases.

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

Connect to the Connections database on the vistrylegacy.database.windows.net SQL Server using SSMS.
Navigate to stored procedures and modify the SP_CONNECTIONS_OBFUSCATE_PURCHASER_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
This will generate the script as a new query. Follow the instructions to identify the 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 Purchaser who has file a right to be forgotten request.

-- 1) Identify the Contact ID for the purchaser.

-- Use the SQL Query below to find the Contact ID.

SELECT id AS CONTACT_ID

FROM CONNECTIONS.contacts

WHERE COALESCE(first_name,'') + COALESCE(last_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.

[dbo].SP_CONNECTIONS_OBFUSCATE_CONTACT_PI_RIGHT_TO_BE_FORGOTTEN @CONTACT_ID = 'Enter Contact 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'

SELECT * FROM CONNECTIONS.contacts WHERE Id = 'Enter Contact ID here'

SELECT * FROM dbo.T_Reservations WHERE ContactID = 'Enter Contact ID here'

SELECT * FROM dbo.T_Contacts WHERE ContactID = 'Enter Contact ID here'

--- ===========================================================================================================

SERUM

Connect to the SERUM database on the vistrylegacy.database.windows.net SQL Server using SSMS.
Navigate to stored procedures and modify the SP_SERUM_OBFUSCATE_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
This will generate the script as a new query. Follow the instructions to identify the ID of the Enquiry 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 person who has filed a right to be forgotten request.

-- 1) Identify the ENQUIRY_NO for the person.

-- Use the query below to find the ID

SELECT PerEnquiryNumber

,PerForenames

,PerMiddleName

,PerSurname

FROM dbo.tblperson

WHERE COALESCE(PerForenames , '')

+ COALESCE(PerMiddleName, '')

+ COALESCE(PerSurname , '') LIKE '%EnterNameHere%'

-- 2) Execute this stored procedure providing the ENQUIRY_NO you found in Step one as the @ENQUIRY_NO parameter.

EXEC dbo.[SP_SERUM_OBFUSCATE_PI_RIGHT_TO_BE_FORGOTTEN] @ENQUIRY_NO = 'Enter ENQUIRY_NO here'

--3) Check that all the PI has been replaced by "-----" by running these queries here.

SELECT * FROM dbo.T_Plots WHERE EnqNumber = 'Enter Enquiry No. Here'

SELECT * FROM dbo.tblperson WHERE EnqNumber = 'Enter Enquiry No. Here'

Epic

Connect to the Epic database on the vistrylegacy.database.windows.net SQL Server using SSMS.
Navigate to stored procedures and modify the SP_EPIC_OBFUSCATE_PI_RIGHT_TO_BE_FORGOTTEN stored procedure.
This will generate the script as a new query. Follow the instructions to identify the ID of the Party 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 person who has filed a right to be forgotten request.

-- 1) Identify the PARTY_ID for the person.

-- Use the query below to find the ID

SELECT PARTY_ID

FROM [AR].[HZ_PARTIES]

WHERE COALESCE(PARTY_NAME ,'')

+ COALESCE(PERSON_FIRST_NAME ,'')

+ COALESCE(PERSON_MIDDLE_NAME ,'')

+ COALESCE(PERSON_LAST_NAME ,'') LIKE '%Thorneycroft%'--LIKE '%Search Name Here%'

-- 2) Execute this stored procedure providing the Party ID you found in Step one as the @PARTY_ID parameter.

EXEC dbo.[SP_EPIC_OBFUSCATE_PI_RIGHT_TO_BE_FORGOTTEN] @PATY_ID = 'Enter PARTY_ID here'

--3) Check that all the PI has been replaced by "-----" by running these queries here.

SELECT * FROM dbo.T_Customers WHERE PARTY_ID = 'Enter Party ID Here'

SELECT * FROM dbo.T_Tags WHERE PARTY_ID = 'Enter Party ID Here'

SELECT * FROM [AR].[HZ_PARTIES] WHERE PARTY_ID = 'Enter Party ID Here'

--- ===========================================================================================================

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'

--- ===========================================================================================================