GRANT, DENY, REVOKE: Understanding SQL Server Permissions

The Three Permission States

In SQL Server, a permission has three possible states for any principal (user, role, group):

State	Meaning	Effect
GRANT	Permission explicitly allowed	User CAN perform the action
DENY	Permission explicitly forbidden	User CANNOT perform the action (overrides GRANT)
No Entry	Neither GRANT nor DENY	User CANNOT perform the action (implicit deny)

Key principle: DENY always wins. Even if a role grants SELECT, a DENY on the user overrides it.

Permission Hierarchy

Permissions flow down from server to database to schema to object. A user's effective permissions at each level are inherited from above.

Server (CONTROL SERVER, IMPERSONATE LOGIN, etc.)
  └─ Database (CREATE TABLE, CREATE PROCEDURE, etc.)
      └─ Schema (ALTER, EXECUTE, etc.)
          └─ Object (SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.)

Example: Permission Inheritance

-- User "app_user" is a member of role "db_datareader"
-- db_datareader is granted SELECT on the entire database

-- The user inherits:
-- SELECT on all tables
-- SELECT on all views
-- Recursively through schema and object

-- But if we DENY SELECT on a specific table:
-- DENY overrides the inherited GRANT
-- User CANNOT SELECT from that specific table
-- But CAN SELECT from all other tables (GRANT still applies)

GRANT: Explicit Allow

Basic Syntax

GRANT  ON  TO ;

Examples

-- Grant SELECT on a table
GRANT SELECT ON dbo.Customers TO app_user;

-- Grant SELECT on all tables in a schema
GRANT SELECT ON SCHEMA::dbo TO app_user;

-- Grant EXECUTE on a stored procedure
GRANT EXECUTE ON dbo.sp_GetCustomers TO app_user;

-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON dbo.Customers TO app_user;

-- Grant with GRANT OPTION (user can grant to others)
GRANT SELECT ON dbo.Customers TO app_user WITH GRANT OPTION;

Common Permissions

Permission	Level	Use Case
`SELECT`	Table/View	Read data
`INSERT`	Table/View	Add rows
`UPDATE`	Table/View	Modify rows
`DELETE`	Table/View	Remove rows
`EXECUTE`	Procedure/Function	Run code
`CREATE TABLE`	Database/Schema	Create schema objects
`ALTER`	Schema/Object	Modify schema objects
`CONTROL`	Any level	Full ownership (implies all permissions)

DENY: Explicit Forbid (The Override)

DENY  ON  TO ;

Examples

-- Forbid DELETE on Customers table (even if user has role grant)
DENY DELETE ON dbo.Customers TO app_user;

-- Forbid UPDATE on sensitive columns
DENY UPDATE ON dbo.Employees (Salary) TO junior_dev;

-- Forbid CREATE TABLE in database
DENY CREATE TABLE TO intern_user;

DENY Is Absolute

-- Setup:
-- app_user is member of role "readers"
-- readers role is GRANTED SELECT on all tables
-- app_user directly receives GRANT SELECT on dbo.Customers

-- Then we execute:
DENY SELECT ON dbo.Customers TO app_user;

-- Result:
-- app_user CANNOT SELECT from dbo.Customers
-- DENY overrides both the inherited GRANT (from readers role)
-- AND the direct GRANT on that specific user

-- Why?
-- DENY is a security boundary. It means "never, regardless of other permissions"

⚠ Common mistake: Developers assume a REVOKE removes a DENY. It doesn't. REVOKE removes GRANT or DENY. After REVOKE, the permission reverts to no entry (implicit deny).

REVOKE: Remove Explicit Permission

REVOKE  ON  FROM ;

Examples

-- Remove explicit GRANT
REVOKE SELECT ON dbo.Customers FROM app_user;

-- Remove explicit DENY
REVOKE DELETE ON dbo.Customers FROM app_user;

-- After REVOKE, permission status:
-- If the user is in a role with the permission, they still have it
-- If not, they lose access (implicit deny)

What REVOKE Actually Does

-- Before REVOKE:
SELECT app_user permission on dbo.Customers: GRANT

-- After REVOKE SELECT ON dbo.Customers FROM app_user:
SELECT app_user permission on dbo.Customers: (no entry)

-- BUT:
-- If app_user is in a role with SELECT GRANTED:
SELECT app_user permission on dbo.Customers (via role): GRANT ← Still inherited!

-- To remove that, REVOKE the role or DENY at user level

Real-World Scenario: The Permission Mess

Initial Setup

-- Create a developer user
CREATE LOGIN dev_john WITH PASSWORD = 'TempPassword123!';
CREATE USER dev_john FOR LOGIN dev_john;

-- Add to db_datareader role (standard read-only access)
ALTER ROLE db_datareader ADD MEMBER dev_john;

-- Dev needs to INSERT into a staging table, so grant extra permission
GRANT INSERT ON dbo.Staging TO dev_john;

-- Dev needs to run a specific procedure
GRANT EXECUTE ON dbo.usp_ImportData TO dev_john;

Current Permissions

dev_john can:
✓ SELECT any table (via db_datareader role)
✓ SELECT any view (via db_datareader role)
✓ INSERT into dbo.Staging (direct GRANT)
✓ EXECUTE dbo.usp_ImportData (direct GRANT)
✗ UPDATE any table (implicit deny)
✗ DELETE any table (implicit deny)
✗ CREATE objects (implicit deny)

Two Months Later: Audit Discovers Unauthorized Access

An auditor finds that dev_john has UPDATE permission on dbo.Staging (a sensitive table with financial data). But nobody granted it explicitly. Where did it come from?

Investigation:

-- Check permissions on the table
SELECT
  permission_name,
  state_desc,
  principal_name
FROM sys.database_permissions dp
INNER JOIN sys.database_principals pr
  ON dp.grantee_principal_id = pr.principal_id
WHERE dp.major_id = OBJECT_ID('dbo.Staging')
  AND principal_name = 'dev_john';

-- Result: Nothing! No direct GRANT or DENY for dev_john on Staging

-- Check roles
SELECT name FROM sys.database_role_members
INNER JOIN sys.database_principals pr ON sys.database_role_members.role_principal_id = pr.principal_id
WHERE member_principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'dev_john');

-- Aha! dev_john is in a role. Check role permissions:
SELECT
  permission_name,
  state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'db_datawriter')
  AND major_id = OBJECT_ID('dbo.Staging');

-- Result: GRANT UPDATE (and INSERT, DELETE) via db_datawriter role
-- Someone added dev_john to db_datawriter by mistake!

Root cause: Someone added dev_john to db_datawriter (which has INSERT, UPDATE, DELETE on all tables). The developer had elevated permissions they didn't need.

Fix:

-- Remove from db_datawriter
ALTER ROLE db_datawriter DROP MEMBER dev_john;

-- Keep only db_datareader
-- Keep the specific GRANT for INSERT on Staging
-- Now dev_john has: SELECT (all) + INSERT (Staging) + EXECUTE (usp_ImportData)

Principle of Least Privilege (PoLP)

Users should have only the minimum permissions needed to do their job. Over-granting leads to:

Accidental data modification or deletion
Compliance violations (SOX, HIPAA, PCI-DSS require auditable access)
Insider threat risk (intentional misuse)
Confusion about who can do what

Better Permissions Model

-- Create custom roles for business functions
CREATE ROLE sales_analysts;
CREATE ROLE finance_auditors;
CREATE ROLE it_admins;

-- Grant minimal permissions
GRANT SELECT ON SCHEMA::dbo TO sales_analysts;
GRANT SELECT ON dbo.Customers TO sales_analysts;
GRANT SELECT ON dbo.Orders TO sales_analysts;
-- (No INSERT, UPDATE, DELETE)

GRANT SELECT ON SCHEMA::dbo TO finance_auditors;
GRANT SELECT ON dbo.FinancialTransactions TO finance_auditors;
-- (Read-only access to specific financial tables)

-- Add users to roles (instead of direct grants)
ALTER ROLE sales_analysts ADD MEMBER david_sales;
ALTER ROLE finance_auditors ADD MEMBER sarah_finance;

-- If a user needs extra access, grant at the user level (explicit)
-- This makes audit trails clearer:
GRANT EXECUTE ON dbo.usp_ExportReport TO david_sales;

Common Permission Pitfalls

Pitfall 1: DENY Without Understanding Precedence

-- Mistake: Trying to prevent a specific action
ALTER ROLE db_owner DROP MEMBER junior_dev;  -- Remove from owner role
GRANT SELECT ON dbo.Customers TO junior_dev;  -- Grant only SELECT

-- But then someone adds junior_dev to db_securityadmin role
-- Which inherits all permissions from the database

-- Result: junior_dev now has more power than db_owner
-- and GRANT SELECT is irrelevant

-- Better: Use DENY to be explicit
ALTER ROLE db_securityadmin DROP MEMBER junior_dev;
GRANT SELECT ON SCHEMA::dbo TO junior_dev;

Pitfall 2: Forgetting Inherited Permissions

-- User is in a role with broad permissions
-- You try to restrict by granting more specific permissions
-- But the role's permissions still apply!

GRANT SELECT ON dbo.Customers TO user_in_datareader_role;
-- This doesn't remove their inherited datareader permissions
-- It just adds a redundant explicit grant

-- To truly restrict, use DENY:
DENY UPDATE ON dbo.Customers TO user_in_datareader_role;

Pitfall 3: Using sysadmin for Everything

-- Anti-pattern: Make application account sysadmin
-- "It's easier, and we'll be more careful"
-- Result: One SQL injection = full server compromise

-- Better: Use service accounts with minimal necessary permissions
-- If the app is an ETL process, it needs:
GRANT CREATE TABLE ON DATABASE::staging TO etl_service;
GRANT SELECT ON DATABASE::production TO etl_service;
GRANT INSERT, DELETE ON DATABASE::staging TO etl_service;

-- NOT sysadmin. Ever.

Auditing Permissions

-- Check who has what permissions
SELECT
  pr.name AS principal_name,
  pr.type_desc,
  perm.permission_name,
  perm.state_desc,
  obj.name AS object_name
FROM sys.database_permissions perm
INNER JOIN sys.database_principals pr
  ON perm.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects obj
  ON perm.major_id = obj.object_id
WHERE pr.name NOT IN ('dbo', 'guest')  -- Exclude system principals
ORDER BY pr.name, perm.permission_name;

-- Check role membership
SELECT
  rm.member_principal_id,
  dp.name AS member_name,
  rm.role_principal_id,
  rp.name AS role_name
FROM sys.database_role_members rm
INNER JOIN sys.database_principals dp
  ON rm.member_principal_id = dp.principal_id
INNER JOIN sys.database_principals rp
  ON rm.role_principal_id = rp.principal_id
ORDER BY rp.name, dp.name;

Integration with Control Matrix

Our Control Matrix provides a comprehensive framework for implementing least-privilege access control across your SQL Server infrastructure. This article's principles directly align with the control objectives defined there.

The Bottom Line

SQL Server permissions are not complicated; they just follow strict, unintuitive rules:

GRANT = "user can do this"
DENY = "user absolutely cannot, no matter what"
REVOKE = "remove the explicit GRANT or DENY" (leaves implicit deny if no other grant)
DENY always wins (over-rides inherited GRANT)
No entry = implicit deny (most restrictive default)

Follow principle of least privilege. Use roles for consistency. Audit regularly. And never, ever make an application account sysadmin.