Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

Lets create a table with MASKED WITH (FUNCTION)

USE <Database Name>
GO
CREATE TABLE Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"-|-|-|",0)') NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

Insert couple of rows

USE <Database Name>
GO
INSERT Membership (FirstName, LastName, Phone#, Email) VALUES ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'), ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'), ('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');

Create a user in database Windows / SQL

CREATE USER test1 FOR LOGIN;

Grant SELECT Permission on Table to User

GRANT SELECT ON Membership TO test1;

Access the table with test1 user

EXECUTE AS USER = 'test1';
SELECT * FROM Membership;
REVERT;

You can connect to the SQL Server Database using the database and access the table, in both the case you will see the same masked data

In case user wants to see the unmasked data , GRANT the UNMASK Permission from user

GRANT UNMASK TO test1;

REVOKE the UNMASK Permission to force the Masking

REVOKE UNMASK TO test1;