Column-Level Permissions: Hiding One Column From Some Users

Employee identifiers and salary values do not belong to every reader's same access boundary. Column-level permissions express that distinction, but their interaction with broader grants and query shapes requires a real caller test.

Define the Allowed Columns before Granting Access

Start with the business interface rather than an existing SELECT star query. Identify which columns the user needs and which values must remain unavailable. Keep row restrictions separate because hiding one column does not restrict which employee rows can be read.

I test the intended caller before accepting a permission change as complete. I also inspect role memberships alongside explicit user grants. A carefully chosen column grant does not undo unrelated broad permissions inherited from another role.

Use an isolated test database for the examples. The users are database principals without logins, allowing controlled impersonation without creating application credentials. Execute the setup as an administrator authorized to manage those demonstration objects.

CREATE TABLE dbo.EmployeeAccess
(
    EmployeeId int NOT NULL PRIMARY KEY,
    EmployeeCode varchar(20) NOT NULL,
    Salary decimal(12,2) NOT NULL
);
INSERT dbo.EmployeeAccess(EmployeeId, EmployeeCode, Salary)
VALUES (1, 'E100', 70000.00), (2, 'E200', 82000.00);
CREATE USER ColumnReader WITHOUT LOGIN;
CREATE USER ExceptSalaryReader WITHOUT LOGIN;
CREATE USER ColumnExceptionReader WITHOUT LOGIN;

These synthetic salaries illustrate a permission boundary, rather than describing real employees. Avoid testing access by printing sensitive production values into an administrator's report. Use approved test data or an appropriately controlled verification method.

Grant SELECT Through Column-Level Permissions

Granting column-level permissions permits queries that reference the named columns under the applicable security context. It does not grant the omitted salary column. Use explicit column lists in the application so the intended interface remains stable.

GRANT SELECT ON OBJECT::dbo.EmployeeAccess
    (EmployeeId, EmployeeCode) TO ColumnReader;

EXECUTE AS USER = N'ColumnReader';
BEGIN TRY
    SELECT EmployeeId, EmployeeCode FROM dbo.EmployeeAccess;
    BEGIN TRY
        SELECT Salary FROM dbo.EmployeeAccess;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS SalaryReadError,
               ERROR_MESSAGE() AS SalaryReadMessage;
    END CATCH;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;

The permitted query returns only the requested non-sensitive columns. The salary query fails with error 230, a column-level SELECT denial. Verify both paths rather than assuming that one successful query demonstrates the complete restriction.

SELECT star requests every selected table column, including Salary. It therefore fails for this user instead of silently removing the forbidden column. SQL Server does not rewrite the projection into a personalized list of visible values.

A predicate or ordering expression referencing Salary also needs the relevant access. Hiding Salary only from the displayed projection does not define every query requirement. Test filtering and sorting paths used by the actual application.

Deny the Sensitive Column under a Broader Table Grant

A table-level grant followed by a salary-column deny is another possible pattern. The column denial blocks salary access even though the table grant otherwise supplies SELECT. Keep the narrower allowlist approach when it expresses the requirement more clearly.

GRANT SELECT ON OBJECT::dbo.EmployeeAccess TO ExceptSalaryReader;
DENY SELECT ON OBJECT::dbo.EmployeeAccess(Salary) TO ExceptSalaryReader;

EXECUTE AS USER = N'ExceptSalaryReader';
BEGIN TRY
    SELECT EmployeeId, EmployeeCode FROM dbo.EmployeeAccess;
    BEGIN TRY
        SELECT * FROM dbo.EmployeeAccess;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS AllColumnReadError,
               ERROR_MESSAGE() AS AllColumnReadMessage;
    END CATCH;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;

DENY normally takes precedence over applicable grants in the permission evaluation. SQL Server has an important compatibility exception for column grants against a table-level deny. State that exception instead of turning the normal rule into an absolute guarantee.

The following deliberately denies the table before granting one column to a separate demonstration user. The narrower grant can override that table-level denial for the column. Review the actual stored entries after each permission change because statement ordering can affect them.

DENY SELECT ON OBJECT::dbo.EmployeeAccess TO ColumnExceptionReader;
GRANT SELECT ON OBJECT::dbo.EmployeeAccess(EmployeeCode)
    TO ColumnExceptionReader;

EXECUTE AS USER = N'ColumnExceptionReader';
BEGIN TRY
    SELECT EmployeeCode FROM dbo.EmployeeAccess;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;

Do not use this exception as an invitation to layer contradictory policies unnecessarily. A clear permitted interface is easier to audit than overlapping grants and denials. The database security model has enough personality without additional improvisation.

Inspect Column-Level Permissions and Effective Access

sys.database_permissions records explicit permission entries. For object-or-column permissions, minor_id zero represents the object and a positive minor_id identifies a column. Join to sys.columns to make that distinction readable.

SELECT grantee.name AS Grantee,
       p.state_desc, p.permission_name,
       OBJECT_SCHEMA_NAME(p.major_id) AS SchemaName,
       OBJECT_NAME(p.major_id) AS ObjectName,
       c.name AS ColumnName
FROM sys.database_permissions AS p
JOIN sys.database_principals AS grantee
    ON grantee.principal_id = p.grantee_principal_id
LEFT JOIN sys.columns AS c
    ON c.object_id = p.major_id AND c.column_id = p.minor_id
WHERE p.class = 1
  AND p.major_id = OBJECT_ID(N'dbo.EmployeeAccess')
ORDER BY grantee.name, p.minor_id, p.permission_name;

The catalog is not a complete effective-permissions calculator. Fixed-role privileges, inherited permissions, ownership, and module execution paths also matter. Run representative queries as the intended caller after reviewing the explicit entries.

An administrator's successful read does not prove the restriction is ineffective. sysadmin and database-owner contexts bypass ordinary permission checks in important ways. Use a nonprivileged principal whose memberships match the actual consumer.

REVOKE removes or adjusts a grant or deny rather than supplying the same semantics as DENY. A removed direct grant can leave access inherited from elsewhere. Inspect the resulting policy and repeat the allowed and forbidden queries.

Consider a View as the Reader Interface

A view with explicit approved columns can provide a simpler consumer contract. Grant SELECT on that view while withholding direct base-table access. Review ownership and execution paths so the view works under the intended permission design.

CREATE OR ALTER VIEW dbo.EmployeeDirectory
AS
SELECT EmployeeId, EmployeeCode
FROM dbo.EmployeeAccess;
GO
CREATE USER DirectoryReader WITHOUT LOGIN;
GRANT SELECT ON OBJECT::dbo.EmployeeDirectory TO DirectoryReader;

A view can also express approved row filters when those rules belong in the interface. Do not assume a column deny on a base table behaves identically through every ownership chain. Test the actual view or procedure that the user invokes.

Keep view definitions explicit when the goal is to limit data exposure. Adding a new sensitive base-table column should not automatically add it to the reader interface. Schema changes require review of dependent views and application projections.

Choose between views and column grants according to how consumers query the data. Column grants can work for direct table access, while a view can define a stable named interface. Both require review of alternate access paths.

Use Masking for Presentation and Verify the Boundary

Dynamic data masking changes displayed values for callers without the relevant unmask permission. It does not remove their SELECT access to the column. Query-based inference remains a concern when callers can issue unrestricted ad hoc SQL.

Use masking to reduce accidental display of sensitive values in an approved query flow. Use permissions and controlled interfaces when access itself must be restricted. Encryption, row security, and auditing address other requirements rather than replacing this column decision automatically.

Can the real reader obtain Salary through another role, view, procedure, export, or predicate? Test the supported access paths using that reader's context. A failed direct SELECT is one piece of evidence, not the complete access audit.

Document column-level permissions with the allowed queries and expected failures. Preserve those checks when role membership or schema definitions change. The restriction is meaningful when the complete supported reader interface respects it.

Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Dynamic Data Masking (DDM) Introduction.

A hidden projection is not a permission boundary, it is an interface that needs verified access controls behind it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.