How to REVOKE Permissions in SQL Server

How do I revoke permissions from users or roles in SQL Server?

REVOKE removes previously granted or denied permissions from SQL Server principals, immediately blocking those actions.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does REVOKE do in SQL Server?

REVOKE removes a permission that was earlier granted or denied. After execution, the affected principal inherits access only through other explicit GRANTs or role memberships, making it invaluable for tightening security without adding DENY entries.

When should I use REVOKE instead of DENY?

Use REVOKE when you simply want to clear a permission so that higher-level role or schema settings can flow down. Use DENY when you need an explicit, hard block that overrides any GRANTs.

How do I write the REVOKE syntax?

Single permission

REVOKE SELECT ON dbo.Products TO SalesReader;

Multiple permissions

REVOKE SELECT, INSERT ON dbo.Orders TO AnalystRole;

Revoking WITH GRANT OPTION

REVOKE GRANT OPTION FOR UPDATE ON dbo.Customers TO dev_user;

Schema-level revoke

REVOKE EXECUTE ON SCHEMA::sales TO app_service;

Can I revoke permissions from several users at once?

Yes. List multiple principals separated by commas: REVOKE SELECT ON dbo.Products FROM Alice, Bob, Carol;

Step-by-step example for ecommerce

Suppose SupportTeam originally had SELECT on Orders. The finance department now handles that report. Revoke access:

REVOKE SELECT ON dbo.Orders TO SupportTeam;

SupportTeam can no longer query Orders unless another role grants it indirectly.

Best practices for REVOKE

Audit before you revoke using sys.database_permissions. Test revokes in staging. Prefer revoking at the role level rather than individual users to keep security manageable.

What happens after a REVOKE?

The engine re-evaluates effective permissions instantly. Users may regain access through other GRANTs, roles, or ownership chains. Verify with sys.fn_my_permissions.

Troubleshooting tips

If a user still accesses data after REVOKE, look for cascading GRANTs from roles, schemas, or the dbo ownership chain. Use sys.database_principals to trace inheritance.

Why How to REVOKE Permissions in SQL Server is important

How to REVOKE Permissions in SQL Server Example Usage


-- Finance now owns sales metrics, revoke prior access
REVOKE SELECT, UPDATE ON dbo.Orders TO SalesDashboardApp;
-- Verify
EXECUTE AS USER = 'SalesDashboardApp';
SELECT * FROM dbo.Orders; -- Should fail
REVERT;

How to REVOKE Permissions in SQL Server Syntax


-- Basic pattern
REVOKE [GRANT OPTION FOR] permission [ ,... ]
    ON [object ::] securable
    { TO | FROM } principal [ ,... ]
    [ CASCADE ]

-- Ecommerce examples
-- 1. Remove SELECT from a report reader
REVOKE SELECT ON dbo.Orders TO ReportReader;

-- 2. Strip INSERT & UPDATE from bulk loader
REVOKE INSERT, UPDATE ON dbo.OrderItems TO BulkLoader;

-- 3. Revoke GRANT OPTION so a dev can’t forward privileges
REVOKE GRANT OPTION FOR SELECT ON dbo.Products TO dev_alice;

Common Mistakes

Using DENY instead of REVOKE. DENY overrides future GRANTs and may break inheritance. Fix by issuing REVOKE and then correctly applying role-based GRANTs.
Forgetting GRANT OPTION. Revoking only the permission leaves users able to grant it to others. Include GRANT OPTION FOR to fully strip delegation rights.

Frequently Asked Questions (FAQs)

Does REVOKE automatically cascade to underlying objects?

Only if you specify the CASCADE option when revoking at a higher securable level, such as a schema. Without CASCADE, underlying object permissions remain.