Question

A reporting analyst needs to query the Employees table in an Azure SQL Database. Business rules allow the analyst to read all columns except the Salary column, which contains sensitive compensation data. You must enforce this using object-level permissions with the least administrative effort, while keeping the analyst able to run SELECT queries that reference other columns of the table. Which T-SQL approach should you use?

Accepted Answer

GRANT SELECT ON dbo.Employees TO analyst; then DENY SELECT ON dbo.Employees(Salary) TO analyst;

Answer

Create a view that omits the Salary column and grant SELECT only on the view, revoking SELECT on the base table.

Answer

Apply Dynamic Data Masking to the Salary column and GRANT SELECT ON dbo.Employees TO analyst;

Answer

GRANT SELECT ON dbo.Employees(Salary) TO analyst; then REVOKE SELECT ON dbo.Employees TO analyst;