Experiment: If I have Dynamic Data Masking enabled will BCP pull the non masked data.

2

July 20, 2021 by Kenneth Fisher

Hypothesis: If I have Dynamic Data Masking enabled on a column then when I use something like BCP to pull the data out it should still be masked.

I’m almost completely certain this will be the case but I had someone tell me they thought it would go differently, and since neither of us had actually tried this out it seemed like time for a simple experiment.

Set up Dynamic Data Masking

USE master;
GO
-- Create a test login
CREATE LOGIN [Ken_Test] WITH PASSWORD = 'Ken_Test', CHECK_POLICY = OFF;
GO
-- Use a test database
USE msdb;
GO
-- Create test user
CREATE USER [Ken_Test] FROM LOGIN [Ken_Test];
GO
-- Create test table with a couple of masked columns
CREATE TABLE [dbo].[Employee]   
  (Id INT NOT NULL IDENTITY (1,1),
   SSN varchar(11) MASKED WITH (FUNCTION = 'default()') NOT NULL,   
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL); 
GO
-- Load data
INSERT INTO [dbo].[Employee] VALUES 
	 ('111-11-1111','ken.fisher@email.com')
	,('222-22-2222','sqlstudent144@gmail.com')
	,('333-33-3333','bob.smith@email.com');
GO
-- Grant select to the temp user
GRANT SELECT ON [dbo].[Employee] TO [Ken_Test];
-- Test the mask
EXECUTE AS USER = 'Ken_Test';
SELECT Id, SSN, Email FROM [dbo].[Employee];
REVERT;
GO

Ok, so obviously the masking is working. Let’s try out BCP.

BCP Test

bcp msdb.dbo.Employee out c:\temp\Employee.csv /S Server\Instance /U Ken_Test /P Ken_Test /c /t,

Success! The output, even using BCP is masked.

Cleanup

USE msdb;
GO
DROP TABLE [dbo].[Employee];
DROP USER [Ken_Test];
DROP LOGIN [Ken_Test];
GO

2 thoughts on “Experiment: If I have Dynamic Data Masking enabled will BCP pull the non masked data.

Leave a Reply to How Dynamic Data Masking Interacts with Bulk Copy (BCP) – Curated SQL Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: