The SELECT permission was denied on the object (SQL Server error 229)

What it means

You’re signed in and the object exists, but the database user you’re running as doesn’t have the permission this statement needs on it. The message names the permission, the object, its database and its schema:

Msg 229, Level 14, State 5
The SELECT permission was denied on the object 'invoices', database 'shop', schema 'dbo'.

The permission can be SELECT, INSERT, UPDATE, DELETE, EXECUTE (for procedures and functions), REFERENCES and others. A related number is 230, for a single column:

Msg 230, Level 14, State 1
The SELECT permission was denied on the column 'salary' of the object 'staff', database 'shop', schema 'dbo'.

Permissions add up from everything you are: your user, every database role you’re in (fixed ones like db_datareader and custom ones), and public. One rule overrides that: a DENY anywhere wins over any GRANT, so being in db_datareader doesn’t help if a role you’re in has a DENY.

229 stops only the statement that hit it; an open transaction stays open.

Common causes

1. No grant at all: a new user, or a new table nobody granted on yet. db_datareader covers SELECT on every table and view, but nothing grants INSERT, UPDATE, DELETE or EXECUTE unless someone does.
2. A read-only account doing a write. Reporting users are often only in db_datareader.
3. A DENY, directly or through a role such as db_denydatareader or db_denydatawriter.
4. Column-level permissions (230): SELECT * on a table where some columns are denied, or where only some columns were granted.
5. Dynamic SQL inside a procedure. Calling a procedure usually needs only EXECUTE, because SQL Server skips checks on objects with the same owner as the procedure (an ownership chain). Statements built and run with EXEC('…') or sp_executesql break the chain and are checked against your permissions.
6. Objects with different owners: a view in one schema over a table in a schema owned by someone else also breaks the chain.

How to fix it

See what you have

As the user who gets the error:

SELECT subentity_name, permission_name
FROM fn_my_permissions('dbo.orders', 'OBJECT');      -- per object; columns in subentity_name

SELECT HAS_PERMS_BY_NAME('dbo.orders', 'OBJECT', 'INSERT') AS can_insert;   -- 1 or 0

And the roles you’re in:

SELECT r.name AS role_name
FROM sys.database_role_members AS m
JOIN sys.database_principals AS r ON r.principal_id = m.role_principal_id
WHERE m.member_principal_id = USER_ID();

An administrator can list what has been granted or denied, and to whom:

SELECT pr.name AS principal, pe.state_desc, pe.permission_name,
       OBJECT_SCHEMA_NAME(pe.major_id) AS schema_name, OBJECT_NAME(pe.major_id) AS object_name,
       COL_NAME(pe.major_id, pe.minor_id) AS column_name
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pe.class = 1   -- objects and columns
ORDER BY object_name, principal;

Grant the permission to a role

Grant to a role and put users in it, rather than granting to each user:

CREATE ROLE app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::sales TO app_writer;   -- the whole schema
GRANT EXECUTE ON SCHEMA::sales TO app_writer;                          -- its procedures
ALTER ROLE app_writer ADD MEMBER [app];

Granting on a schema also covers tables added to it later. For one object: GRANT SELECT ON dbo.orders TO app_writer;. If what you need is a write on a production database from a read-only account, the error is doing its job: use the account meant for writes.

Remove the DENY, if it’s wrong

REVOKE SELECT ON dbo.staff (salary) FROM [app];     -- removes the column-level DENY
ALTER ROLE db_denydatareader DROP MEMBER [app];

REVOKE removes a GRANT or a DENY; it doesn’t deny anything. For column-level DENYs that are deliberate (salaries, personal data), list the columns you may read instead of SELECT *.

Keep procedures inside the ownership chain

Prefer static SQL in procedures. If you need dynamic SQL, give the procedure the rights instead of every caller: sign it with a certificate, or create it WITH EXECUTE AS OWNER (understand what that lets callers do first). Keep the objects a procedure or view uses in schemas with the same owner.

Reproduce it

On SQL Server 2022 (RTM-CU27, 16.0.4295.3), as the login reader, which is only in db_datareader in database inlet, on our own table seo_sqlerr_conn.orders and a procedure seo_sqlerr_conn.close_order. First the checks, which said no write was possible:

SELECT HAS_PERMS_BY_NAME('seo_sqlerr_conn.orders', 'OBJECT', 'INSERT') AS can_insert,
       HAS_PERMS_BY_NAME('seo_sqlerr_conn.orders', 'OBJECT', 'UPDATE') AS can_update,
       HAS_PERMS_BY_NAME('seo_sqlerr_conn.orders', 'OBJECT', 'DELETE') AS can_delete,
       HAS_PERMS_BY_NAME('seo_sqlerr_conn.close_order', 'OBJECT', 'EXECUTE') AS can_execute;

can_insert can_update can_delete can_execute
---------- ---------- ---------- -----------
0 0 0 0

fn_my_permissions listed only SELECT, on the table and each column. Then each statement, inside BEGIN TRAN … ROLLBACK:

Msg 229, Level 14, State 5, Server 7732422b7f56, Line 2
The INSERT permission was denied on the object 'orders', database 'inlet', schema 'seo_sqlerr_conn'.
Msg 229, Level 14, State 5, Server 7732422b7f56, Line 1
The UPDATE permission was denied on the object 'orders', database 'inlet', schema 'seo_sqlerr_conn'.
Msg 229, Level 14, State 5, Server 7732422b7f56, Line 1
The DELETE permission was denied on the object 'orders', database 'inlet', schema 'seo_sqlerr_conn'.
Msg 229, Level 14, State 5, Server 7732422b7f56, Procedure seo_sqlerr_conn.close_order, Line 1
The EXECUTE permission was denied on the object 'close_order', database 'inlet', schema 'seo_sqlerr_conn'.

After the denied INSERT, @@TRANCOUNT was still 1 and the next statement in the batch ran. Two neighbours of 229 showed up too: CREATE TABLE as reader got Msg 262 … CREATE TABLE permission denied in database 'inlet'., and a query on the database sales, where reader has no user, got Msg 916 … The server principal "reader" is not able to access the database "sales" under the current security context. The table was unchanged afterwards. SQL Server 2019 (15.0.4490.9) and 2025 (17.0.5005.3) gave the same errors.

On a temporary SQL Server 2022 container (same build), where we could create a user seo_app: with GRANT SELECT on dbo.staff and DENY SELECT on its salary column, SELECT id, name worked and SELECT * got:

Msg 230, Level 14, State 1, Server 68a606085b65, Line 3
The SELECT permission was denied on the column 'salary' of the object 'staff', database 'seo_perm', schema 'dbo'.

A table with no grant gave The SELECT permission was denied on the object 'invoices'…, and adding the user to db_denydatareader made even the granted SELECT id, name FROM dbo.staff fail with 229. With only EXECUTE granted, a procedure running SELECT id, total FROM dbo.invoices worked, while one running the same query through EXEC (N'…') got The SELECT permission was denied on the object 'invoices', database 'seo_perm', schema 'dbo'.

In Inlet

On connections tagged production, Inlet opens read-only and refuses writes before they’re sent (SQL Server has no read-only session of its own), so a stray UPDATE there is stopped by Inlet rather than by a 229; temp tables and reporting procedures such as sp_help still work. With your own Anthropic API key, Ask Claude (⌘L) can write the GRANT you need from the error and the schema. Managing logins and users isn’t in Inlet yet. When a statement fails with 229, the error links to this page.