7.2 Change Data Tracking and Dynamic Data Masking

Why Both Controls Appear Together

Both Change Tracking/CDC and Dynamic Data Masking appear under the secure-environment domain because they answer operational compliance questions: what changed in this table? and who is allowed to see the unmasked value? They are easy to confuse with audit (which records events, not values) and with Always Encrypted (which protects data rather than hiding it). The exam will mix scenarios to test whether you pick the lightweight tracking option, the heavy historical capture option, or the masking option.

Change Tracking (CT) vs Change Data Capture (CDC)

Both features detect row changes, but they differ sharply in overhead, scope, and what they record.

CT uses the CHANGETABLE table-valued function. CHANGETABLE(CHANGES, ...) returns the rows that changed since a baseline version; CHANGETABLE(VERSION, ...) returns the current version for a specific row. When TRACK_COLUMN_UPDATED = ON is set at the database level, CT also reports which columns changed (via the SYS_CHANGE_COLUMNS column), but it does not record the previous values - only that the column was touched.

CDC is heavier but complete. The capture process reads the transaction log, decodes the operation, and writes a row into a change table named cdc.<schema>_<table> (or cdc.<schema>_<table>_<capture_instance> when a capture instance other than the default is used). Query functions expose the changes:

• cdc.fn_cdc_get_all_changes_<capture_instance> returns one row per change (including intermediate values).
• cdc.fn_cdc_get_net_changes_<capture_instance> returns the net effect over a range (only the final state).

The LSN range helpers sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn bound the query window. A SQL Agent job (the capture job, plus a cleanup job) drives capture on SQL Server and SQL MI; on Azure SQL Database CDC uses a managed background scheduler, so no Agent job is required.

Enabling and Disabling

CT:

ALTER DATABASE Sales SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Per table
ALTER TABLE dbo.Customer ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON);

ALTER TABLE dbo.Customer DISABLE CHANGE_TRACKING;
ALTER DATABASE Sales SET CHANGE_TRACKING = OFF;

CDC:

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name   = 'Customer',
    @role_name     = 'cdc_reader',
    @supports_net_changes = 1;

EXEC sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name   = 'Customer',
    @capture_instance = 'dbo_Customer';
EXEC sys.sp_cdc_disable_db;

A capture instance scopes the change table. When you re-enable CDC after a schema change, you can create a new capture instance (e.g., dbo_Customer_v2) so the old change table keeps serving historical queries while the new one captures the post-change schema. A common exam trap: dropping a column from a CDC-enabled table fails unless you disable the capture instance first or add a new one that excludes the dropped column.

When to Use Which

Use CT when the consumer needs to know that a row changed and which columns changed since a known version, e.g., a mobile client refreshing a cache. Use CDC when the consumer needs the actual old and new values - ETL into a warehouse, an audit log, or building a slowly-changing dimension. CT is the right answer when an exam scenario says "a SaaS application syncs a local copy of reference data and only needs to know which rows changed since the last sync." CDC is the right answer when the scenario requires history of every value change for compliance.

Dynamic Data Masking (DDM)

Dynamic Data Masking limits the exposure of sensitive data to non-privileged users by masking the values in the result set. Crucially, the data is stored unmasked on disk; DDM is a presentation-layer control that rewrites the output based on the caller's permissions. This distinction is one of the most heavily tested facts in the chapter.

Four masking functions cover the common column shapes:

Adding, Modifying, and Removing Masks

Masks are added when the column is created or altered:

CREATE TABLE dbo.Customer (
    CustomerID   int IDENTITY PRIMARY KEY,
    FullName     nvarchar(100) MASKED WITH (FUNCTION = 'default()'),
    Email       nvarchar(200) MASKED WITH (FUNCTION = 'email()'),
    Phone       nvarchar(20)  MASKED WITH (FUNCTION = 'partial(2, 2, "XXXXXX")'),
    Age         int           MASKED WITH (FUNCTION = 'random(18, 99)')
);

ALTER TABLE dbo.Customer ALTER COLUMN FullName ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE dbo.Customer ALTER COLUMN FullName DROP MASKED;

A schema modification trap: to change an existing mask, you ALTER the column and ADD MASKED with the new function. To remove the mask, ALTER the column and DROP MASKED. The column itself must be alterable - masks cannot be added to computed columns, FILESTREAM, sparse columns, or columns protected by Always Encrypted.

UNMASK and Guest Users

UNMASK is a database-level permission (GRANT UNMASK TO NurseRole). A user without UNMASK sees the masked result for every masked column, with two important consequences the exam tests:

• Guest users are masked by default. If a database user has only SELECT on a masked column and no UNMASK, the query returns masked data - DDM does not require an explicit deny.
• UNMASK is all-or-nothing at the database scope. You cannot grant UNMASK on a single column or table; the user either sees all masked columns unmasked or none. For finer-grained control, combine DDM with row-level security or column-level permissions.

To inspect masks, query sys.masked_columns:

SELECT object_name(object_id) AS tbl, name AS col, is_masked, masking_function
FROM sys.masked_columns
WHERE is_masked = 1;

DDM vs Always Encrypted

Both controls protect sensitive data, but they operate at different layers, and the exam relies on the contrast:

The recurring trap: a scenario describes DBAs and server admins as adversaries. DDM is the wrong answer - it never protects data from a privileged user who can read the underlying storage or query with UNMASK. Always Encrypted is the right answer because the column master key lives in the client application; the server never sees plaintext.

DDM Limitations

DDM has documented limitations worth memorizing for scenario rejection:

• Masking does not apply to columns encrypted with Always Encrypted.
• Masking does not work for computed or persisted computed columns that depend on a masked column; the output may be undefined or unmasked.
• A masked column in a WHERE clause uses the unmasked value at the server, so a user can infer data by querying "WHERE Age > 50" and observing counts.
• Aggregate functions over masked columns return masked or partial results and can leak distribution information.
• Masking is not a security boundary; the documentation states it is for compliance with privacy exposure rules, not for protecting data from a determined adversary.
• BULK INSERT and similar operations still load unmasked values; the mask is enforced on read, not write.

Takeaways

Pick CT for lightweight "did it change?" sync; pick CDC for full-value history with capture instances. Pick DDM for casual visibility control of query results; pick Always Encrypted when even the DBA must not see plaintext. Remember UNMASK is database-scoped, masks are added and dropped through ALTER COLUMN, and DDM never changes what is stored on disk.

DDM Quiz

The second quiz below tests the mask-vs-Always-Encrypted distinction and the UNMASK scope, which together account for most masking traps in DP-300 scenarios.

Operational Reminder

Before enabling either CT or CDC on a production table, consider the secondary cost: CT adds overhead to every DML because the version is bumped synchronously, while CDC adds storage growth (the change table is roughly the size of the source table per retention window) and requires monitoring the capture job health. For DDM, remember that mask choice is reversible - you can alter the masking function later without data movement because no data is stored in masked form.

A final scenario pattern: a report user must see the last 4 digits of a credit card for fraud review but nothing else. The correct control is partial(0, 4, 'XXXXXX') (no prefix, four suffix digits, padding in between), granted with SELECT but without UNMASK. If the same user must see the full PAN, the correct control is to grant UNMASK or to redesign with Always Encrypted, not to remove the mask.