How do you grant and revoke specific permissions, such as SELECT or EXECUTE, on individual database objects rather than through broader roles?

Intermediate

Answer

You use the GRANT statement to give a specific permission on a specific object to a user or role, and the REVOKE statement to remove a previously granted permission, giving you fine grained control when you need to allow or restrict access to just one particular table, view, or stored procedure rather than an entire schema or database.

GRANT SELECT ON dbo.Orders TO SalesUser;
GRANT EXECUTE ON dbo.GetOrderSummary TO SalesUser;
REVOKE SELECT ON dbo.Orders FROM SalesUser;

Real-world example A support team member is granted permission to execute a specific reporting stored procedure without being given broader access to directly query the underlying sensitive tables that procedure relies on internally.

Common follow-ups: What is the difference between REVOKE and DENY in SQL Server?;Can permissions granted directly to a user conflict with permissions granted through a role they belong to?

Views;Stored Procedures & Functions

All 7 SQL Server Security & Permissions questions