July 20, 2021 by Kenneth Fisher
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','firstname.lastname@example.org') ,('222-22-2222','email@example.com') ,('333-33-3333','firstname.lastname@example.org'); 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 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.
USE msdb; GO DROP TABLE [dbo].[Employee]; DROP USER [Ken_Test]; DROP LOGIN [Ken_Test]; GO