{"id":8868,"date":"2016-09-19T15:19:13","date_gmt":"2016-09-19T13:19:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-dynamic-data-masking-and-database-role\/"},"modified":"2023-06-06T10:53:03","modified_gmt":"2023-06-06T08:53:03","slug":"sql-server-2016-dynamic-data-masking-and-database-role","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-dynamic-data-masking-and-database-role\/","title":{"rendered":"SQL Server 2016: Dynamic Data Masking and database role"},"content":{"rendered":"

Last week, dbi services organized an event named \u201cSQL Server 2016: what\u2019s new?\u201d in Lausanne, Basel and Zurich. I would take the opportunity to say again a big thank you to everyone which joined us.
\nDuring 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.
\nLet\u2019s try to clarify that.<\/p>\n

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:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n<\/td>\n
\n
\n
-- Create database<\/code><\/div>\n
USE MASTER<\/code><\/div>\n
GO<\/code><\/div>\n
CREATE DATABASE DDM_TEST<\/code><\/div>\n
GO<\/code><\/div>\n
<\/div>\n
-- Create the Confidential table<\/code><\/div>\n
USE [DDM_TEST]<\/code><\/div>\n
GO<\/code><\/div>\n
<\/div>\n
CREATE TABLE [dbo].[Confidential](<\/code><\/div>\n
[ID] [<\/code>int<\/code>] NULL,<\/code><\/div>\n
[Name] [n<\/code>var<\/code>char](<\/code>70<\/code>)NULL,<\/code><\/div>\n
[CreditCard] [<\/code>var<\/code>char](<\/code>16<\/code>)NULL,<\/code><\/div>\n
[Salary] [<\/code>int<\/code>] NULL,<\/code><\/div>\n
[Email] [n<\/code>var<\/code>char](<\/code>60<\/code>)NULL<\/code><\/div>\n
)ON [PRIMARY]<\/code><\/div>\n
<\/div>\n
-- Insert some rows<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'1'<\/code>,<\/code>'Stephane'<\/code>,<\/code>'3546748598467584'<\/code>,<\/code>113459<\/code>,<\/code>'sts@dbi-services.com'<\/code>)<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'2'<\/code>,<\/code>'David'<\/code>,<\/code>'3546746598450989'<\/code>,<\/code>143576<\/code>,<\/code>'dab@dbi-services.com'<\/code>)<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'3'<\/code>,<\/code>'Nathan'<\/code>,<\/code>'3890098321457893'<\/code>,<\/code>118900<\/code>,<\/code>'nac@dbi-services.com'<\/code>)<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'4'<\/code>,<\/code>'Olivier'<\/code>,<\/code>'3564890234785612'<\/code>,<\/code>98000<\/code>,<\/code>'olt@dbi-services.com'<\/code>)<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'5'<\/code>,<\/code>'Alain'<\/code>,<\/code>'9897436900989342'<\/code>,<\/code>85900<\/code>,<\/code>'ala@dbi-services.com'<\/code>)<\/code><\/div>\n
INSERT INTO [dbo].[Confidential] VALUES (<\/code>'6'<\/code>,<\/code>'Fabrice'<\/code>,<\/code>'9083234689021340'<\/code>,<\/code>102345<\/code>,<\/code>'fad@dbi-services.com'<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I will create some masks for confidential columns with the following script:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
-- create data masking <\/code>for<\/code> confidential columns <\/code>with<\/code> masking functions<\/code><\/div>\n
USE DDM_TEST<\/code><\/div>\n
GO<\/code><\/div>\n
<\/div>\n
ALTER Table Confidential<\/code><\/div>\n
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = <\/code>'random(1, 150000)'<\/code>)<\/code><\/div>\n
ALTER Table Confidential<\/code><\/div>\n
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION=<\/code>'partial(1,\"XXXX\",2)'<\/code>)<\/code><\/div>\n
ALTER Table Confidential<\/code><\/div>\n
ALTER COLUMN Email ADD MASKED WITH (FUNCTION=<\/code>'email()'<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I create now a user and add him to db_datareader role:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n<\/td>\n
\n
\n
-- Create a user named TestDemo and add <\/code>this<\/code> user to the db_datareader role<\/code><\/div>\n
USE DDM_TEST<\/code><\/div>\n
go<\/code><\/div>\n
CREATE USER TestDemo WITHOUT LOGIN<\/code><\/div>\n
USE [DDM_TEST]<\/code><\/div>\n
GO<\/code><\/div>\n
ALTER ROLE [db_datareader] ADD MEMBER [TestDemo]<\/code><\/div>\n
GO<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Let\u2019s try to visualize the Confidential table with my user:<\/p>\n

\"DDM_res1\"<\/a><\/p>\n

Member of the db_datareader role are able to query the Confidential table but cannot see masked columns without masks.
\nLet\u2019s try to add my user to the db_datawriter role and rerun my query:<\/p>\n

\"DDM_res2\"<\/a><\/p>\n

Same, and it\u2019s normal as db_datawriter cannot change read possibility, but the user is now able to insert data in the Confidential table like this for example:<\/p>\n

\"DDM_res3\"<\/a><\/p>\n

But, it cannot view in clear text data that it has inserted.
\nLet’s add my user to the db_owner database role:<\/p>\n

\"DDM_res4\"<\/a><\/p>\n

He can now view masked columns.
\nIn term of permissions for data masking we have:<\/p>\n