// SQL Server error reference

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
Error message
The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

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 DENY on the object, the schema or a role the user is in (for example db_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

  1. 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;
  2. Check what the user actually has on the object.

    EXECUTE AS USER = 'app_user';
    SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
    REVERT;
  3. Look for DENY entries 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';
  4. 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

More permissions errors

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.

// fewer errors, faster fixes

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.