How to implement Column-Level Security in SQL Server

João Barros 08 de July de 2026 4 min read

Protecting sensitive columns — such as salaries, tax IDs or contact details — is a common need in any database. Column-Level Security in SQL Server lets you decide, column by column, who can see what, without duplicating tables or maintaining awkward views. With GRANT and DENY permissions applied directly to columns, you control access with just a few lines of T-SQL and keep a single source of data.

Prerequisites

A SQL Server instance (2016 or later) or Azure SQL Database.
SQL Server Management Studio (SSMS) or Azure Data Studio.
Administrator permissions on the database to create users and grant permissions.
Basic T-SQL knowledge (SELECT and CREATE TABLE).

Step 1: Create a sample table

For this example, we create an employees table with a sensitive column — Salario — that we want to hide from the support team, while keeping the name and department visible.

CREATE TABLE dbo.Funcionarios
(
    FuncionarioID INT PRIMARY KEY,
    Nome          NVARCHAR(100),
    Departamento  NVARCHAR(50),
    Salario       DECIMAL(10,2)
);

INSERT INTO dbo.Funcionarios VALUES
(1, N'Ana Silva',   N'Financeiro', 3200.00),
(2, N'Bruno Costa', N'Suporte',    1800.00);

Step 2: Create a user and a role

Next, we create a database user and a role. Assigning permissions to a role instead of to each individual person is a good governance practice: when someone joins or leaves the team, you simply add or remove the role member.

-- Database user without login (simple for testing)
CREATE USER SuporteUser WITHOUT LOGIN;

-- Role for the support team
CREATE ROLE SuporteRole;
ALTER ROLE SuporteRole ADD MEMBER SuporteUser;

Step 3: Grant access only to the allowed columns

This is the heart of Column-Level Security. Instead of granting SELECT on the whole table, we grant SELECT only on the columns the team may query. The Salario column is simply left off the list.

GRANT SELECT ON dbo.Funcionarios(FuncionarioID, Nome, Departamento) TO SuporteRole;

This approach is safer and easier to maintain than creating a view with only the permitted columns, because the rules live inside SQL Server's own permission engine. Column-level permissions work with SELECT, UPDATE and REFERENCES.

Step 4: Block a column with DENY (alternative pattern)

Sometimes it is more practical to grant access to almost everything and block just one or two columns. In that case, grant SELECT on the table and use DENY on the sensitive column. It is important to know that DENY always takes priority over GRANT: even with permission on the table, the denied column stays inaccessible.

GRANT SELECT ON dbo.Funcionarios TO SuporteRole;
DENY SELECT ON dbo.Funcionarios(Salario) TO SuporteRole;

Choose only one of the patterns — the one from Step 3 (allow list) or the one from Step 4 (deny list) — depending on whether it is shorter to list what is allowed or what is forbidden.

Verify the result

You do not need to sign in as another user to test. Use EXECUTE AS to take on the SuporteUser identity in the same window and REVERT to return to your own user. Notice that a SELECT with an asterisk fails, because it includes the protected column.

EXECUTE AS USER = 'SuporteUser';

-- This WORKS (only permitted columns)
SELECT FuncionarioID, Nome, Departamento FROM dbo.Funcionarios;

-- This FAILS: "The SELECT permission was denied on the column 'Salario'"
SELECT * FROM dbo.Funcionarios;

REVERT;

If the first query returns the rows and the second ends with a permission error on the Salario column, then Column-Level Security is configured correctly.

Conclusion

In just a few minutes you restricted access to a sensitive column without duplicating data or creating extra objects. The natural next step is to combine this technique with Row-Level Security, to control rows and columns at the same time, or to organize permissions into per-department roles to simplify management. Which column in your database should be protected starting today?