Home/Error Library/sqlserver-229-permission-denied-select

SQL Server

SQL Server 2016–2022Security & Privileges

8 min read

Msg 229, Level 14, State 5: The SELECT permission was denied on the object

SQL Server Msg 229 — Who is Blocking the Effective Permission?

Diagnosing Msg 229: resolving effective permissions with sys.fn_my_permissions, DENY precedence, broken ownership chains, and the guest user trap.

Reviewed by:Principal Database Reliability Architect

Tested Versions: SQL Server 2022 CU15 & 2019 CU28 (Windows Server / RHEL 9 Docker)

Operational Verification & Reliability Metadata

Last Technical Review: 30 Ekim 2026

Tested VersionsSQL Server 2022 & 2019 (Docker 29.7, Windows Server 2022)

Required PrivilegesCONTROL or ALTER ANY ROLE / GRANT privileges

Risk Assessment

MediumAdding GRANT over a principal with an inherited DENY is futile; DENY always trumps GRANT in SQL Server

Expected OutputAccurate effective permission evaluation via sys.fn_my_permissions, resolving DENY overrides and guest user fallback

Rollback Procedure:REVOKE conflicting DENY and establish structured schema-level role grants

Microsoft Docs: Database Engine Permissions & Ownership Chaining

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Customer', database 'app', schema 'dbo'.

The prefix adapts based on the target securable and operation:

plaintext

The EXECUTE permission was denied on the object 'sp_Report', database 'app', schema 'dbo'.
The INSERT permission was denied on the object 'Customer', database 'app', schema 'dbo'.
The SELECT permission was denied on the column 'SSN', database 'app', schema 'dbo'.

This represents the SQL Server counterpart to PostgreSQL's permission denied for table (H-34). However, authorization resolution in SQL Server follows a distinct three-tier pipeline: Role Inheritance + DENY Precedence + Ownership Chaining.

▸Diagnostic Queries to Confirm Root Cause

1. Identify Runtime Context (The Guest User Trap!)

sql

SELECT SUSER_SNAME() AS server_login, USER_NAME() AS db_user;

IMPORTANT

The Guest User Trap: If db_user evaluates to guest, your server-level login is valid, but no database user mapping exists for it in the target catalog. Rather than severing the session, SQL Server downgrades your execution context to the restricted guest user, immediately provoking Msg 229.

2. Query Effective Securable Permissions

Verify runtime permissions directly rather than traversing nested role hierarchies manually:

sql

SELECT * FROM sys.fn_my_permissions('dbo.Customer', 'OBJECT');

3. Inspect for Overriding DENY Declarations

In SQL Server security semantics: DENY ALWAYS TRUMPS GRANT. If a user belongs to five roles with GRANT SELECT and a single role with DENY SELECT, the effective permission is unequivocally denied:

sql

SELECT prin.name AS principal, perm.permission_name, perm.state_desc
FROM sys.database_permissions perm
JOIN sys.database_principals prin ON prin.principal_id = perm.grantee_principal_id
WHERE perm.major_id = OBJECT_ID('dbo.Customer')
  AND perm.state_desc = 'DENY';

▸Broken Ownership Chains

A frequent point of confusion: A user has EXECUTE permissions on sp_Report. The procedure starts executing, but throws Msg 229 when referencing an internal table.

How Ownership Chaining Operates: When an executing module accesses an underlying table, and both securables share the identical schema owner (dbo), SQL Server bypasses intermediate table-level permission checks.

When the Chain Breaks:

1Divergent Schema Owners: If the procedure resides in dbo and the referenced table in sales, ownership chaining breaks, triggering table-level permission checks.
2Dynamic SQL Execution: Statements evaluated inside EXEC() or sp_executesql run in the security context of the caller, completely bypassing the stored procedure's ownership chain.

▸Production Resolution

1. Schema-Level Role Grants

sql

CREATE ROLE app_reader;
GRANT SELECT ON SCHEMA::dbo TO app_reader;
ALTER ROLE app_reader ADD MEMBER app_user;

2. Remediate Orphaned Database Users

sql

USE app;
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE app_reader ADD MEMBER app_user;

3. Revoke Conflicting DENY Permissions

sql

REVOKE SELECT ON dbo.Customer FROM reporting_role;

4. Impersonation Smoke Testing

sql

EXECUTE AS USER = 'app_user';
SELECT TOP 1 * FROM dbo.Customer;
REVERT;

▸Related Error Guides & Hub Links

•PostgreSQL Permission Denied for Table (H-34) — Bilateral PostgreSQL counterpart
•SQL Server Cannot Open Database (H-15) — Login database access failures
•SQL Server Hub — Security engineering and role-based access control

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (50)

SQLSERVER5 min