Where Learning is Pleasure

Jayaram Krishnaswamy

Subscribe to Jayaram Krishnaswamy: eMailAlertsEmail Alerts
Get Jayaram Krishnaswamy: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Blog Feed Post

Data Masking a Column in a Table

Here is what I am going to do.

Here is a query that retrieves FirstName, LastName, City from Employees table in the Northwind database on my SQL Server 2016 SP2 named instance OHANA.

Use Northwind
Go
SELECT FirstName, LastName, City
FROM Employees


Now I will apply a datamask to the FirstName column with a default() datamask using the following Alter Table, Alter Column statements.
-----------------------
Use Northwind
Go
Alter table Employees
Alter Column FirstName nvarchar(10) Masked with (Function='default()')
-----------------------------
When I run the above query, the query runs without errors. whenever I run the query, the data is not masked for me, because I have permissions.

To test the masking I create a user, hacker3 as follows:
--
/* create a hacker3 user*/
CREATE USER hacker3 WITHOUT LOGIN;
GRANT SELECT ON EMPLOYEES TO hacker3;
EXECUTE AS USER='hacker3'
----

DataMask_01
 Now I run a SELECT query, the same one we used earlier and get this response.


DataMask_02

You will observe that the FirstName column is masked. Note that the FirstNames in the table ranges from having 4 to 8 letters but the default() masking function replaces them with 3 xxx's.

Note that if you run a new query, you are not running as hacker3.



Read the original blog entry...

More Stories By Jayaram Krishnaswamy

Jayaram Krishnaswamy is a technical writer, mostly writing articles that are related to the web and databases. He is the author of SQL Server Integration Services published by Packt Publishers in the UK. His book, 'Learn SQL Server Reporting Services 2008' was also published by Packt Publishers Inc, Birmingham. 3. "Microsoft SQL Azure Enterprise Application Development" (Dec 2010) was published by Packt Publishing Inc. 4. "Microsoft Visual Studio LightSwitch Business Application Development [Paperback] "(2011) was published by Packt Publishing Inc. 5. "Learning SQL Server Reporting Services 2012 [Paperback]" (June 2013) was Published by Packt Publishing Inc. Visit his blogs at: http://hodentek.blogspot.com http://hodentekHelp.blogspot.com http://hodnetekMSSS.blogspot.com http://hodnetekMobile.blogspot.com He writes articles on several topics to many sites.