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

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:tempEmployee.csv /S ServerInstance /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