Create a mask script per table to mask SQL Server data

Applies to

ApexSQL Mask


This article explains how to create a single script per table to mask SQL Server data.


This database masking tool provides an opportunity to mask all sensitive data from one database. Those sensitive data can be replaced with automatically generated data by using the static type masking or can be hidden from non -privileged users by using the dynamic type masking without changing the original data from the database.

When columns with sensitive data are selected and have chosen the corresponding type of mask for masking or hiding that data, ApexSQL Mask offers next three processes for masking that sensitive data:

  • Update database – the sensitive data in the chosen database will be masked by directly updating directly database
  • Create a single script – a masking script will be created and can be used later to mask the sensitive data in the selected database
  • Create a script per table – the mask script will be created for each table in the selected database

In case of creating a single script, if the database is large, the following issues can arise:

  • The newly created script will take up a lot of space. Which is not suitable for sharing between teams
  • System.Out.Of.Memory error may occur

The option to create a single script per table will help to overcome the swept issues.

In the article the procedure to create a masking script for every chosen table to mask SQL Server data will be explained.

Establishing database connection

On the application start the Connect to SQL Server dialog will be open. The SQL Server instance, the type of the Authentication, and the desired Database should be chosen in this dialog. If all is set, click on the Connect button:

The Connect to SQL Server window

Choosing appropriate method to mask SQL Server data

The view will go to the main window of ApexSQL Mask, which shows automatically detected sensitive columns with their automatically assigned masks in the main grid:

The main ApexSQL Mask window

Beside automatically assigned masks, ApexSQL Mask offers the possibility of creating new masks for masking and hiding sensitive data. The process of creating a new mask will be started with a click on the Manage button in the Home tab:

The Manage button in the Home tab

The Manage masks window will appear, offering the ability to create:

  • Static data masks
  • Dynamic data masks

The Manage masks window

Static data masks

Static data masks can be created, edited, and deleted in the Replace original data tab. From the Replace original data tab can be chosen two types of static data masks:

The Replace original data tab

Dynamic data masks

To create, edit, and delete a dynamic data mask, click on the Hide original data tab. The settings in this tab allow users to create a dynamic type of data mask. By creating this mask type, sensitive data will be hidden from the non-privileged users, without changing that data in the database. More about dynamic data masking feature can be found in the Mask SQL Server data using dynamic data masking article:

The Hide original data tab

Creating mask script per each table

When masks are created and applied to the selected columns, the next step is to run the mask process. Click on the Process button on the Home tab, will start the masking process:

The Process button in the Home tab

The Process mask window will appear, whereby click on the drop-down list can be chosen the type of process. For purposes of this article will be chosen the Create script per table process:

The Process mask dialogue

This action will create a separate script to mask SQL Server data for every table that contains the sensitive data that needs to be masked. With running those scripts, users can mask sensitive data for every table separately thus reducing the risk of encountering errors due to manipulation of large amount of data in a single execution.