The SELECT Permission Was Denied on the Object (Error 229)

This is the last door in the corridor. The login worked (that gate was 18456), the database let the principal in (that one was 916), and now a single object has said no. The message is the most precise of the family: it names the permission, the object, the database and the schema, which means the diagnosis is already half done and the remaining question is not what failed but why this user was expected to have access and does not.

Why This Happens

• The user was created and never granted anything. A CREATE USER with no follow-up is a valid user with no rights, which is exactly how the lab reproduced the message above.
• Access came through a role that changed. The user used to inherit SELECT through a role or an AD group, and membership got tidied up.
• A DENY is winning. DENY beats GRANT everywhere, always. A user granted SELECT directly but sitting in a role that carries a DENY still gets error 229.
• An ownership chain broke. A view or procedure used to reach the table through a chain (same owner end to end, so the table was never checked); a schema or ownership change broke the chain, and now the table’s own permissions suddenly apply.

See What the User Actually Has

Run as someone with permission to look, with the affected database in context:

SELECT pr.name          AS principal_name,
       pe.state_desc,
       pe.permission_name,
       OBJECT_NAME(pe.major_id) AS object_name
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = N'ErrDemo229'
   OR pr.principal_id IN (SELECT role_principal_id
                          FROM sys.database_role_members
                          WHERE member_principal_id = USER_ID(N'ErrDemo229'));

An empty result is the never-granted case. A row with state_desc = 'DENY' is your answer regardless of what else is granted. For the full effective picture of one account, token and all, the Get User Permissions Audit script does the whole job in one pass.

The Fix Hierarchy

Fix it at the highest sensible level, not the lowest:

• 1. Role membership first. If a reader role exists (or db_datareader fits the need), membership is one line and survives object churn: ALTER ROLE db_datareader ADD MEMBER [ErrDemo229];
• 2. Schema-level grant where the need is “everything in this schema”: GRANT SELECT ON SCHEMA::dbo TO [ErrDemo229];
• 3. Object-level grant only for genuinely one-off access: GRANT SELECT ON dbo.RlsOrders TO [ErrDemo229];
• 4. Ownership chains are the fix when the intent is “access through the view or procedure only, never the table”: keep the chain’s ownership aligned rather than granting on the base table and giving away more than intended.

Object-by-object grants accumulate into an unauditable pile; every step up this hierarchy is one less line someone has to understand in five years.

When It Is Not This

If the message says the database itself refused the principal, that is error 916 and the user mapping, not object permissions, is the problem. If it is a server-level view or DMV being refused, that is VIEW SERVER STATE territory, which has its own post. And EXECUTE, INSERT, UPDATE and DELETE denials are this same error with a different verb, and the same hierarchy applies.

Common Questions

Related Scripts

• Not Able to Access the Database (916), the same family one door earlier
• Grant VIEW SERVER STATE, the server-level version of this conversation
• Get User Permissions Audit, one account’s effective access in a single script
• Get Permissions and Role Membership, the broad grant enumeration