SQL Server 2016: Dynamic Data Masking and database role

Last week, dbi services organized an event named “SQL Server 2016: what’s new?” in Lausanne, Basel and Zurich. I would take the opportunity to say again a big thank you to everyone which joined us.
During my session some questions concerning the new functionality Dynamic Data Masking were asked. In fact data are masked for some roles and not for some others.
Let’s try to clarify that.

I will use the same script I used during the event to initialize the database, create the table and fill-in my table with some rows:

-- Create database
USE MASTER
GO
CREATE DATABASE DDM_TEST
GO

-- Create the Confidential table
USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential](
[ID] [int] NULL,
[Name] [nvarchar](70)NULL,
[CreditCard] [varchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL
)ON [PRIMARY]

-- Insert some rows
INSERT INTO [dbo].[Confidential] VALUES ('1','Stephane','3546748598467584',113459,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('2','David','3546746598450989',143576,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('3','Nathan','3890098321457893',118900,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('4','Olivier','3564890234785612',98000,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('5','Alain','9897436900989342',85900,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('6','Fabrice','9083234689021340',102345,'[email protected]')

I will create some masks for confidential columns with the following script:

-- create data masking for confidential columns with masking functions
USE DDM_TEST
GO

ALTER Table Confidential
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'random(1, 150000)')
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()')

I create now a user and add him to db_datareader role:

-- Create a user named TestDemo and add this user to the db_datareader role
USE DDM_TEST
go
CREATE USER TestDemo WITHOUT LOGIN
USE [DDM_TEST]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestDemo]
GO

Let’s try to visualize the Confidential table with my user:

Member of the db_datareader role are able to query the Confidential table but cannot see masked columns without masks.
Let’s try to add my user to the db_datawriter role and rerun my query:

Same, and it’s normal as db_datawriter cannot change read possibility, but the user is now able to insert data in the Condifendial table like this for example:

But, it cannot view in clear text data that it has inserted.
The only way to visualize data without masks is to be a member of the db_owner role:

Conclusion is clear, in order to be able to view masked columns user has to be member of the db_owner role in the database containing the table.
Happy masking 😉