GDPR schema extensions
======================
GDPR schema extensions address de-identification of users/principals and accounts. 

GDPR related scripts are in the following files:

1. obfuscate_principal.sql
2. obfuscate_principals_bulk.sql
3. obfuscate_principal_list.sql

A. Instructions/examples
----------------------------

A.1 De-identifying single principal/user
----------------------------------------

File (1) is a pre-requisite - it is needed in order to create the stored procedure ObfuscatePrincipal, which is called by all other script files. This stored procedure can also be used on a stand-alone basis. If you intend to de-identify a few users on-demand, and occassionally, this is all you will need. 

The parameters are stated in file (1), a summary of which is as follows:

@principalId is the unique ID (USER_ID/PRINCIPAL_ID) of the user to target

@days is number of days elapsed since user was disabled. For instance, if you need to be sure you are de-identifying users who were deleted through the UI in the last six months, set this value to 180 days (default). This will cause the user to be de-identified if she was deleted through the UI in last 180 days. Otherwise, if the user was deleted earlier, you will receive error message.

@result Character message having result. Message OK means everything went as expected.

@scoNamePrefix, optional parameter. This is the prefix to use while de-identifying the user's personalized folders. The default prefix, ‘zzzz-‘, is sufficient for most purposes, and it is not recommended that you change this.

Example script:
USE [my_database]
GO
DECLARE @RC int
DECLARE @principalId bigint = 123456
DECLARE @days int = 180
DECLARE @result nvarchar(2000)
EXECUTE @RC = [dbo].[ObfuscatePrincipal] 
   @principalId
  ,@days
  ,@result OUTPUT
PRINT @RC
PRINT @output
GO

A.2 De-identifying all disabled principals belonging to an account
------------------------------------------------------------------

In order to de-identify all disabled users belonging to a specific account, create the stored procedure ObfuscatePrincipalsBulk by executing script file (2). Before calling the stored procedure, you will need to know the account ID, and provide the parameter @days, as stated in the previous section. This procedure, in turn, calls ObfuscatePrincipal with these parameters.

Example script:
USE [my_database]
GO
DECLARE @RC int
DECLARE @accountId bigint = 123
DECLARE @days int = 180
DECLARE @result nvarchar(2000)

EXECUTE @RC = [dbo].[ObfuscatePrincipalsBulk] 
   @accountId
  ,@days
  ,@result OUTPUT

PRINT @RC
PRINT @output
GO

A.3 De-identifying principals by login
--------------------------------------

In order to de-identify one or more specific users by login, execute the SQL script file (3). Since this script file targets specific users, it requires the script file to be edited first with the appropriate parameters. In addition to the parameters already discussed in the earlier sections, you will also need to provide a list of logins (optionally, with account IDs) and specify them at the beginning of the script. Further instructions and examples can be found in the script itself.

Note that, while this script allows multiple logins (e.g. user1@domain.com, user2@domain.com) to be targeted, it does not allow for multiple users to be targeted based on a single login (e.g. two deleted instances of janedoe@company.com). In the event that there is more than one match for a single login (e.g., in multi-tenant environments, or the result of previously deleted users with the same login) the script will raise an error. In order to proceed, you will need to further refine the login with either a specific account ID or a narrower time range in order to target a single user per login as specified in the script.

