SQL Server 2016: Does Dynamic Data Masking works with INSERT INTO and SELECT INTO commands?

I wonder how works Dynamic Data Masking (DDM) with these two commands INSERT INTO  and SELECT INTO.

First, I create a table and add some “sensitive data”:

USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Name] [nvarchar](70)NULL,
[CreditCard] [nvarchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL)  


insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'Stephane',N'3546748598467584',113459,N'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'David',N'3546746598450989',143576,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Nathan',N'3890098321457893',118900,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Olivier',N'3564890234785612',98000,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Alain',N'9897436900989342',85900,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Fabrice',N'908323468902134',102345,'[email protected]')

select * from [dbo].[Confidential]

After, I create all masking rules and add a user:

Use DDM_TEST
ALTER Table Confidential
ALTER COLUMN NAME ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN SALARY ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN creditcard ADD MASKED WITH (FUNCTION='partial(1,"XXXX",2)')
ALTER Table Confidential
ALTER COLUMN email ADD MASKED WITH (FUNCTION='email()')

CREATE USER TestDemo WITHOUT LOGIN
GRANT SELECT ON Confidential TO TestDemo

-- Execute a select statement as TestDemo 
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
REVERT

INSERT INTO

This command is used to copy a table.
What’s happens when I copy data from a table with masked columns to a table without mask?
First, I create a second table [dbo].[Confidential2] and give permissions SELECT and INSERT to the user “TestDemo”

USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential2](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Name] [nvarchar](70)NULL,
[CreditCard] [nvarchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL)  

GRANT SELECT ON Confidential2 TO TestDemo
GRANT INSERT ON Confidential2 TO TestDemo

I execute the query to insert data from [dbo].[Confidential] to [dbo].[Confidential2] with the INSERT INTO command:

USE [DDM_TEST]
GO
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential]
INSERT INTO [dbo].[Confidential2]([Name],[CreditCard],[Salary],[Email])
	SELECT [Name],[CreditCard],[Salary],[Email] FROM [dbo].[Confidential]
SELECT * FROM [dbo].[Confidential]
REVERT

As you can see data are also masked in the second table [dbo].[Confidential2].
But are they really?
I execute the query with the activation on the query plan.

As you can see the masking step is missing in the query plan from the select on [dbo].[Confidential2].
If I Select data from [dbo].[Confidential2] with my admin account, data are “masked data” and not real data…

Finally, the goal is reached, you cannot read sensitive data if you copy data from a table to another.
Keep in mind that the copied data are not masked for the user.
These data are copied as “masked data” and guarantee the anonymization and a good security for your sensitive data.
But on the other side, if you use the same WHERE clause in [dbo].[Confidential2], you don’t have the same result… 🙁

SELECT INTO

With this command, I test also the copy to a temporary table.
These two cases will be interesting…
I recreate the same table [dbo].[Confidential] with the same masking rules, the user with create table and alter any schema permissions to do the select into:

EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
SELECT * INTO [dbo].[Confidential2] FROM [dbo].[Confidential] ;
REVERT

In the query plan, you can see that the masking is between the select and the insert.
We are in the same case as previously: copied data are “masked data”.
To see it, I read data from the table [dbo].[Confidential2] with my sysadmin login:

And the result is that all copied data are masked. The data remain anonymous.

Finally, let’s test it with a temporary table and let’s see what happens:

USE [DDM_TEST]
GO
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
SELECT * INTO #Confidential2 FROM [dbo].[Confidential] ;
REVERT
EXECUTE AS USER='TestDemo'
SELECT * FROM #Confidential2 
REVERT

The same query plan is applied and masked data are copied and remained anonymous.

Finally, these two commands INSERT INTO and SELECT INTO keep your data anonymous in the case of a Table copy.

Sorry but cheat mode is disabled … 👿