SQL Space

[cc lang=”SQL”]

— master database
use master
go

create table PhoneTable (
cid int identity(1,1) not null,
PhoneNumber char(12)
)

insert into PhoneTable
values (‘238-555-0197’), (‘664-555-0112’)

— unmasked for user with better than SELECT permissions on the table
select * from PhoneTable

— now alter table to mask the PhoneNumber column
ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = ‘partial(4, “xxx-xxxx”, 0)’) NULL;
–ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = ‘default()’) NULL;

— create a user without login who has only SELECT on the table
CREATE USER user1 WITHOUT LOGIN;
GRANT SELECT ON OBJECT::dbo.PhoneTable TO user1

— masked for user who has only READ access on the table
EXECUTE AS USER = ‘user1’;
SELECT *
FROM PhoneTable;
REVERT;

— verify the DDM applied in the entire database
SELECT OBJECT_NAME(object_id) TableName,
name ColumnName,
masking_function MaskFunction
FROM sys.masked_columns
ORDER BY TableName, ColumnName;

— cleanup
drop table PhoneTable
drop user user1

[/cc]