Data governance: access control with GRANT in SQL

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

Controlling who can read and change each table is one of the pillars of data governance. The principle of least privilege says each person should have only the permissions strictly needed for their job: no more, no less. With SQL Server's GRANT, REVOKE and DENY commands, and using roles to organise permissions, this principle becomes simple to apply and easy to audit. Beyond reducing security risks, this approach makes audits easier and helps meet regulations such as GDPR, which require knowing who accesses which data.

Prerequisites

A SQL Server instance or an Azure SQL Database.
SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
An account with administrative permissions (for example, a member of db_owner) to create roles and grant permissions.
Basic SQL knowledge: SELECT, tables and schemas.

Step 1: Create a role to group users

Instead of granting permissions person by person, create one role per business function. That way, when someone joins or changes teams, you only touch the role and not dozens of accounts. Let's create a role for the sales analysts.

CREATE ROLE analistas_vendas;

The role starts with no permissions at all. That is exactly what we want: begin from zero and add only what is necessary.

Step 2: Grant only what is needed with GRANT

The GRANT command assigns a permission. To respect least privilege, we give read only (SELECT) and only over the sales data, not the entire database.

-- Ler todas as tabelas do esquema "vendas"
GRANT SELECT ON SCHEMA::vendas TO analistas_vendas;

-- Permissão mais fina: apenas uma tabela
GRANT SELECT ON OBJECT::vendas.encomendas TO analistas_vendas;

The SCHEMA:: operator applies the permission to all current and future tables in the schema, while OBJECT:: acts on a single table. Choose the right level depending on the granularity you need. Notice we did not grant INSERT, UPDATE or DELETE: an analyst who only needs to query data should not be able to change it.

Step 3: Add users to the role

Now we link people to the role. Each member inherits exactly the role's permissions, with no exceptions.

ALTER ROLE analistas_vendas ADD MEMBER [maria];
ALTER ROLE analistas_vendas ADD MEMBER [joao];

If another analyst joins tomorrow, the process is a single line. You can check which roles a user belongs to at any time in the database properties or with queries against the system catalog. This simplicity is what keeps governance sustainable over time.

Step 4: Block sensitive data with DENY

Sometimes we want to give access to a schema but protect a specific table holding personal data (PII). That is where DENY comes in, and it takes precedence over GRANT.

-- Mesmo com SELECT no esquema, bloquear a tabela de dados pessoais
DENY SELECT ON OBJECT::vendas.clientes_pii TO analistas_vendas;

Golden rule: when a DENY and a GRANT exist for the same permission, the DENY wins. It is the ideal safety net for protecting sensitive data.

Step 5: Remove permissions with REVOKE

Roles change. REVOKE removes a granted permission (or cancels a DENY), leaving the role in a neutral state for that permission.

-- Retirar uma permissão de escrita concedida por engano
REVOKE INSERT ON SCHEMA::vendas FROM analistas_vendas;

-- Retirar um utilizador do papel
ALTER ROLE analistas_vendas DROP MEMBER [joao];

Do not confuse REVOKE with DENY: REVOKE removes the existing rule, whereas DENY actively blocks, even if a GRANT exists.

Check the result

There are two simple ways to confirm everything is correct: query the permissions catalog and test in the user's shoes with EXECUTE AS.

-- Ver as permissões atribuídas ao papel
SELECT dp.permission_name, dp.state_desc, o.name AS objeto
FROM sys.database_permissions AS dp
LEFT JOIN sys.objects AS o ON o.object_id = dp.major_id
WHERE dp.grantee_principal_id = DATABASE_PRINCIPAL_ID('analistas_vendas');

-- Testar as permissões como se fosse a utilizadora
EXECUTE AS USER = 'maria';
SELECT TOP (1) * FROM vendas.encomendas;    -- deve funcionar
SELECT TOP (1) * FROM vendas.clientes_pii;  -- deve falhar (DENY)
REVERT;

Run each query and watch the result. A permission error on one of the lines is not a bug in the tutorial: it is governance doing its job. If Maria can read vendas.encomendas but is blocked on vendas.clientes_pii, least privilege is working as expected.

Conclusion

With roles, GRANT, DENY and REVOKE you have the essential pieces to apply least privilege and keep data governance under control. The next step is to review these permissions periodically and combine them with data masking (Dynamic Data Masking) and sensitivity labels for complete protection. Also document which role grants access to what; that documentation is worth its weight in gold on audit day. Looking at your own database, which table deserves a DENY today?