| title | sp_pdw_log_user_data_masking (Azure Synapse Analytics) | |
|---|---|---|
| description | sp_pdw_log_user_data_masking configures user data masking in Azure Synapse Analytics activity logs. | |
| author | WilliamDAssafMSFT | |
| ms.author | wiassaf | |
| ms.reviewer | randolphwest | |
| ms.date | 06/23/2025 | |
| ms.service | sql | |
| ms.topic | reference | |
| dev_langs |
|
|
| monikerRange | >=aps-pdw-2016 || =azure-sqldw-latest |
[!INCLUDE applies-to-version/asa-pdw]
Use sp_pdw_log_user_data_masking to enable user data masking in [!INCLUDE ssazuresynapse-md] activity logs. User data masking affects the statements on all databases on the appliance.
Important
The [!INCLUDE ssazuresynapse-md] activity logs affected by sp_pdw_log_user_data_masking are certain [!INCLUDE ssazuresynapse-md] activity logs. sp_pdw_log_user_data_masking doesn't affect database transaction logs, or [!INCLUDE ssNoVersion] error logs.
Syntax for Azure Synapse Analytics and Analytics Platform System (PDW).
sp_pdw_log_user_data_masking [ [ @masking_mode = ] value ]
[ ; ]
Note
[!INCLUDE synapse-analytics-od-unsupported-syntax]
In the default configuration, [!INCLUDE ssazuresynapse-md] activity logs contain full [!INCLUDE tsql] statements, and can in some cases include user data contained in operations such as INSERT, UPDATE, and SELECT statements. If there's a problem on the appliance, this permits the analysis of the conditions that caused the problem without a need to reproduce the issue. In order to prevent the user data from being written to [!INCLUDE ssazuresynapse-md] activity logs, customers can choose to turn on the user data masking by using this stored procedure. The statements are still written to [!INCLUDE ssazuresynapse-md] activity logs, but all the literals in statements that might contain user data are masked; replaced with some predefined constant values.
When transparent data encryption is enabled on the appliance, masking of the user data in [!INCLUDE ssazuresynapse-md] activity logs is automatically turned on.
Determines whether transparent data encryption log user data masking is enabled. masking_mode is int, and can be one of the following values:
| Value | Description |
|---|---|
0 |
Disabled, user data appears in the [!INCLUDE ssazuresynapse-md] activity logs. |
1 |
Enabled, user data statements appear in the [!INCLUDE ssazuresynapse-md] activity logs but the user data is masked. |
2 |
Statements containing user data aren't written to the [!INCLUDE ssazuresynapse-md] activity logs. |
Executing sp_pdw_log_user_data_masking without parameters returns the current state of transparent data encryption (TDE) log user data masking on the appliance as a scalar result set.
User data masking in [!INCLUDE ssazuresynapse-md] activity logs enables replacement of literals with predefined constant values in SELECT and Data Manipulation Language (DML) statements, as they can contain user data. Setting masking_mode to 1 doesn't mask metadata, such as column names or table names. Setting masking_mode to 2 removes statements with metadata, such as column names or table names.
User data masking in [!INCLUDE ssazuresynapse-md] activity logs is implemented in the following way:
-
TDE and user data masking in [!INCLUDE ssazuresynapse-md] activity logs are turned off by default. The statements aren't automatically masked if database encryption isn't enabled on the appliance.
-
Enabling TDE on the appliance automatically turns on the user data masking in [!INCLUDE ssazuresynapse-md] activity logs.
-
Disabling TDE doesn't affect user data masking in [!INCLUDE ssazuresynapse-md] activity logs.
-
You can explicitly enable user data masking in [!INCLUDE ssazuresynapse-md] activity logs by using the
sp_pdw_log_user_data_maskingprocedure.
Requires membership in the sysadmin fixed database role, or CONTROL SERVER permission.
The following example enables TDE log user data masking on the appliance.
EXECUTE sp_pdw_log_user_data_masking 1;