How GRANT, REVOKE, and Roles Work in SQL Server

In SQL Server, GRANT gives a security principal a permission, while REVOKE removes an existing grant or denial. DENY is different: it explicitly blocks a permission. For maintainable access control, grant the smallest required permissions to a database role, then add users to that role.

The examples below use Transact-SQL. Permission syntax and role behavior differ in Oracle, PostgreSQL, MySQL, and other database systems.

Principals, securables, and permissions

A permission statement has three essential parts:

• Principal: the login, database user, or role receiving the permission.
• Securable: the server, database, schema, table, view, or procedure being protected.
• Permission: an allowed action such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE.

A login is a server-level identity; a user represents an identity inside a database. Microsoft’s Database Engine principals guide explains the distinction.

Grant a permission on one object

The following grants a database user permission to read one table:

GRANT SELECT ON OBJECT::dbo.Employee TO User1;

Qualifying the object with its schema makes the target unambiguous. You can grant multiple permissions in one statement:

GRANT SELECT, INSERT ON OBJECT::dbo.Employee TO User1;

WITH GRANT OPTION lets the recipient grant that permission to other principals. Use it only when delegation is an intentional administrative requirement. The full object-level syntax is in Microsoft’s GRANT Object Permissions reference.

Revoke a permission

REVOKE SELECT ON OBJECT::dbo.Employee FROM User1;

REVOKE removes the permission entry at that scope. It does not guarantee that the user can no longer select the table: the same permission may still be inherited from another role, granted on the containing schema, or supplied through another authorization path.

If a permission was delegated with WITH GRANT OPTION, review downstream grants before revoking it. SQL Server supports CASCADE in the relevant syntax when dependent grants must also be removed; read the REVOKE Object Permissions documentation before using it.

REVOKE is not DENY

Use DENY sparingly because permission inheritance can become difficult to reason about. Microsoft also documents an important SQL Server exception involving a table-level DENY and a column-level GRANT; do not build a security design around assumed precedence without testing it.

Prefer roles for groups of users

Suppose analysts need read-only access to objects in a schema named reporting. Create a database role, grant the role access at schema scope, and add each database user as a member:

CREATE ROLE report_reader AUTHORIZATION dbo;
GO

GRANT SELECT ON SCHEMA::reporting TO report_reader;
GO

ALTER ROLE report_reader ADD MEMBER Analyst1;
GO

When another analyst joins, add that user to the same role instead of copying individual grants. To remove the user’s role membership:

ALTER ROLE report_reader DROP MEMBER Analyst1;

To remove the schema permission from everyone in the role:

REVOKE SELECT ON SCHEMA::reporting FROM report_reader;

In SQL Server, CREATE ROLE role_name does not use a role password. Microsoft documents role creation and ownership in the CREATE ROLE reference.

Check effective access before and after a change

Review explicit database permission entries:

SELECT
    USER_NAME(grantee_principal_id) AS principal_name,
    state_desc,
    permission_name,
    class_desc,
    OBJECT_SCHEMA_NAME(major_id) AS schema_name,
    OBJECT_NAME(major_id) AS object_name
FROM sys.database_permissions
ORDER BY principal_name, permission_name;

Also inspect role membership and test with an appropriately controlled account. A catalog row alone may not describe every effective permission because roles, ownership, higher-scope permissions, and sysadmin membership can affect the result.

Least-privilege checklist

• Grant access to a custom role rather than directly to many users.
• Prefer schema- or object-level permissions over broad database or server privileges.
• Avoid granting permissions to public unless every database user truly needs them.
• Do not use WITH GRANT OPTION for ordinary application or reporting accounts.
• Review role memberships and effective permissions regularly, especially after job changes.
• Run permission changes through a tested, auditable deployment process.

To build a practice schema safely, start with CREATE DATABASE and CREATE TABLE. You can then test read-only access using the examples in TipsMake’s SQL filtering guide.