SQL Server error 229: Permission denied on object
You can reach the database, but you lack SELECT, INSERT, UPDATE, DELETE or EXECUTE permission on the named object or its schema.
- Error number
229- Severity
- 14
- Category
- Permissions
What it means
Database users only get the permissions granted to them, their roles or public. Error 229 names the permission that was missing (SELECT, EXECUTE...) and the object, database and schema. A DENY anywhere in the chain wins over any GRANT.
Common causes
- The user or its roles were never granted the permission.
- A
DENYon the object, the schema or a role the user is in (for exampledb_denydatareader). - Ownership chaining is broken: a view or procedure references an object owned by a different owner, so the caller's own permission on the underlying table is checked.
- Dynamic SQL inside a procedure runs with the caller's permissions, not the procedure owner's.
How to fix it
-
Grant the permission on the object, or on the whole schema.
GRANT SELECT ON dbo.Orders TO app_user; GRANT EXECUTE ON SCHEMA::dbo TO app_user;
-
Check what the user actually has on the object.
EXECUTE AS USER = 'app_user'; SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT'); REVERT; -
Look for
DENYentries that override grants.SELECT USER_NAME(grantee_principal_id) AS grantee, permission_name, state_desc, OBJECT_NAME(major_id) AS object_name FROM sys.database_permissions WHERE state = 'D'; -
For dynamic SQL in a procedure, sign the procedure with a certificate or use
EXECUTE AS OWNER, instead of granting table access to every caller.
Related errors
262Permission denied in database916Login can't access the database42501PostgreSQL: permission denied1142MySQL: Command denied to user for tableORA-01031Oracle: insufficient privileges
More permissions errors
15151Object doesn't exist or no permission15023User, group, or role already exists15025The server principal already exists15138User owns a schema, cannot be dropped
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.