"An alternative masking solution
In closing, I would offer a different solution to data masking, one that also works with older versions of SQL Server:
Add a computed column that does the masking. Hashing is great for some purposes, but you may want to roll your own text string logic for visual reports and things.
Then grant column-level permissions to your security principals, allowing them to read only the masked and non-sensitive columns in the table.
CREATE TABLE dbo.SensitiveStuff (
ID int NOT NULL,
CustomerName varchar(100) NOT NULL,
--- Here's the computed column:
Customer_masked AS HASHBYTES('SHA2_256', CustomerName),
CONSTRAINT PK_SensitiveStuff PRIMARY KEY CLUSTERED (ID)
--- Assign column-level permissions
GRANT SELECT ON dbo.SensitiveStuff(ID, Customer_masked) TO trevor;
Now, when Trevor wants to read from the table, he’ll get a permission error when he tries to read the CustomerName column, but he can see the hashed customer name in the Customer_masked column.
It’s not precisely the same functionality as Dynamic Data Masking (because you need to assign different column names depending on if you want the masked/unmasked data), but it allows you to set up a security model where some principals have access to masked data and others can see the plain value."
Posted from Diigo. The rest of my favorite links are here.