Data Mask in SQL Server

Hi, here we know about how to show confidential data using Data Mask in SQL Server.

USE tempdb
GO

-- Create a sample table [dbo].[Customer] with masked columns:

CREATE TABLE dbo.Customer (
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(250),
LastName VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
PhoneNumber VARCHAR(12) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXX",0)') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
CreditCardNo VARCHAR(16) MASKED WITH (FUNCTION = 'partial(4,"XXXXXXXXXX",2)') NULL,
);

-- Now insert some test records (fictitious figures):

INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, Email, CreditCardNo) VALUES
('Arka', 'Gupta', '7001061958', 'arkagupto@gmail.com', '4563123456789012'),
('Rahul', 'Singh', '9842359873', 'rahul@gmail.com', '123456789092'),
('Visal', 'Sharma', '6700091129', 'visal@gmail.com', '567890123469'),
('Rajib', 'Agrawal', '9800002000', 'rajib@gmail.com', '345678901224');

-- 1. Now let’s create a Test Account and just Grant Read access to [dbo].[Customer] table:

CREATE USER user1 WITHOUT LOGIN;
GO

GRANT SELECT ON dbo.Customer TO user1;
GO

EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO

-- 2. Removing Masking from a column by simple ALTER TABLE/COLUMN statement:

ALTER TABLE dbo.Customer
ALTER COLUMN LastName DROP MASKED;
GO

-- Let's check the table data again:

EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO

-- 3. Granting the UNMASK permission to “user1”:

GRANT UNMASK TO user1;
GO

-- Let's check the table data again:
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO

-- 4. REVOLE UNMASK TO user1:

REVOKE UNMASK TO user1;
GO
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO

-- 5. FINAL CLEANUP:

DROP TABLE dbo.Customer;
GO
DROP USER [user1]
GO