Skip to content

Est. 2011ยทMicrosoft Partner ID 7033487ยทDelivery under 3 minยทSupport 7 days a week

Your vault is empty.

Free Fix 229

Error 229 SELECT Permission Denied: Granting Rights Without Over-Granting

12 min read Updated October 5, 2026 SQL Server

Fix it now

The connection got in and the statement was refused. The published message names the permission, the object, the database and the schema, so it is telling you exactly what to grant. The only real decision is where to apply it, so you solve today’s problem without handing out far more than the query needs.

Run on the same connection that failed, in the same database

SELECT SUSER_SNAME() AS login, USER_NAME() AS db_user;
SELECT * FROM fn_my_permissions('Sales.Orders','OBJECT');
SELECT HAS_PERMS_BY_NAME('Sales.Orders','OBJECT','SELECT') AS can_select;
  1. Read the message. It names the permission, the object, the database and the schema. That is your target, not the database as a whole.
  2. Confirm who the query actually ran as. An application using a pooled connection or an EXECUTE AS context is often not the principal you assume.
  3. Grant at role level rather than to the user: CREATE ROLE app_read; GRANT SELECT ON SCHEMA::Sales TO app_read; ALTER ROLE app_read ADD MEMBER [appuser];
  4. Re-test as that user without waiting for anyone: EXECUTE AS USER = 'appuser'; SELECT TOP 1 * FROM Sales.Orders; REVERT;

Schema level is usually the right compromise. It survives tables created tomorrow and it does not reach across the database, which is what a database-level grant does.

If the statement now runs as that user, you are done. If it still fails, the next section covers DENY, ownership chaining and the errors that look like this one and are not.

Why it happens

SQL Server checks permission when a statement is compiled and again as it executes, against the effective permissions of the current security context. That context is a database user derived from the login, plus every database role and Windows group it inherits from. The permission must be present somewhere in that set, and no DENY may exist anywhere in it, because one DENY overrides every GRANT.

Permissions are hierarchical. A grant on the server applies to every database, a grant on the database to every schema, a grant on a schema to every object in it including ones created tomorrow, and a grant on an object only to that object. Choosing a level is the actual work here, and it is why the same error gets fixed well by one person and badly by another.

The related numbers vary the scope rather than the idea. 230 is the same refusal at column level. 262 is published as permission denied in the named database, which is what you meet when someone tries to create an object. 297 and 15247 both say only that the user does not have permission to perform this action. 300 has the same shape as 229, a permission denied on an object in a database, and it is the form a VIEW SERVER STATE refusal takes, because the object it names is the server.

That last point is worth being careful about, because 300 is widely described as a server-scoped error and the published message is not scoped that way. Read the object and database it names rather than assuming. A refusal naming the server and master is the monitoring-account case; one naming a table is an ordinary object grant.

The user simply has no grant on the object

You have this one if fn_my_permissions on the object returns nothing, and the same query works for an administrator.

  1. Create a role for the access pattern rather than granting to the user: CREATE ROLE app_read;
  2. Grant what the application needs, at schema level if it reads a whole area: GRANT SELECT ON SCHEMA::Sales TO app_read;
  3. Add the user: ALTER ROLE app_read ADD MEMBER [appuser];
  4. Re-test with EXECUTE AS rather than by asking the user to try again.

Roles are the difference between a permission model you can read in a year and a list of individual grants nobody dares change.

A DENY somewhere is overriding the grant

You have this one if The user is a member of a role that clearly has the permission, and it still fails.

  1. List explicit permissions in the database and look for DENY entries covering the object, the schema or the principal.
  2. Check every group the login belongs to; a DENY on one inherited group beats grants on all the others.
  3. Remove the DENY with REVOKE, which erases the entry. Issuing a GRANT on top of it does not win.
SELECT pr.name AS principal,
       pe.state_desc,
       pe.permission_name,
       OBJECT_SCHEMA_NAME(pe.major_id) + '.' + OBJECT_NAME(pe.major_id) AS object_name
FROM   sys.database_permissions AS pe
JOIN   sys.database_principals  AS pr ON pe.grantee_principal_id = pr.principal_id
WHERE  pe.state_desc = 'DENY';

A stored procedure works and the dynamic SQL inside it does not

You have this one if Users can execute the procedure, and it fails on the statement built with EXEC or sp_executesql.

  1. Ownership chaining lets a procedure read tables the caller cannot, but only where procedure and tables share an owner and the statement is static. Dynamic SQL is a new batch, checked against the caller.
  2. Sign the module with a certificate and grant the permission to the certificate’s principal, so the elevation is scoped to that one procedure.
  3. Use EXECUTE AS OWNER only where you are comfortable with everything in the procedure running elevated.

The object appears not to exist rather than being denied

You have this one if The user is told the object cannot be found, while an administrator can see it perfectly well.

  1. Grant VIEW DEFINITION where the user legitimately needs the object’s metadata.
  2. Check the name is not resolving against the wrong schema; an unqualified name resolves against the user’s default schema first, then dbo.
  3. Set a sensible default schema: ALTER USER [appuser] WITH DEFAULT_SCHEMA = Sales;

SQL Server hides objects a principal has no permission on, so being told something does not exist is often a permission answer in disguise.

A server-level permission is missing

You have this one if A refusal naming the server and master while reading a dynamic management view, rather than a refusal naming one of your tables.

  1. Grant the minimum that works: GRANT VIEW SERVER STATE TO [monitor];
  2. For read-only inspection of definitions across the instance, consider GRANT VIEW ANY DEFINITION TO [monitor];
  3. Do not substitute sysadmin. It is a different order of privilege and it cannot be scoped.

Full reference

Where the permission belongs

Scope Grant When it is the right level
Object GRANT SELECT ON Sales.Orders TO app_read; One table or view, and you want it kept tight
Column GRANT SELECT ON Sales.Orders(OrderId) TO app_read; Where a table is mostly readable and one column is not
Schema GRANT SELECT ON SCHEMA::Sales TO app_read; An application that reads a whole functional area, including future objects
Database GRANT CREATE TABLE TO app_dev; A database-scoped right, which is what 262 is about
Server GRANT VIEW SERVER STATE TO [monitor]; Monitoring accounts reading DMVs

The numbers, precisely

Error What Microsoft publishes Severity
229 The named permission was denied on the object, in the named database and schema 14
230 The named permission was denied on a column of the object 14
262 The named permission is denied in the named database 16
297 The user does not have permission to perform this action 16
300 The named permission was denied on the object, in the named database 14
15247 User does not have permission to perform this action 16

297 and 15247 carry effectively the same sentence and no detail, which is a useful thing to know in advance: when you get one of them there is nothing in the message to work from, and the route forward is to impersonate the principal and ask the engine directly rather than to read the error more carefully.

Do not resolve any of this by adding the login to sysadmin or the user to db_owner. It works instantly and it is why so many applications run with rights nobody can withdraw later. A permission error is a request for one grant, not for administrative access to the instance.

Testing without waiting for the user

-- What does this user actually hold on this object?
EXECUTE AS USER = 'appuser';
SELECT * FROM fn_my_permissions('Sales.Orders','OBJECT');
SELECT HAS_PERMS_BY_NAME('Sales.Orders','OBJECT','SELECT') AS can_select;
REVERT;

-- Everything granted in this database, by principal
SELECT pr.name, pe.state_desc, pe.permission_name, pe.class_desc
FROM   sys.database_permissions AS pe
JOIN   sys.database_principals  AS pr
       ON pe.grantee_principal_id = pr.principal_id
ORDER BY pr.name;

fn_my_permissions takes a securable and a securable class, and the documented classes include OBJECT, SCHEMA, DATABASE and SERVER, with NULL defaulting to SERVER. Passing the wrong class is the usual reason it returns nothing surprising: asking for OBJECT permissions on a schema name answers a question you did not mean to ask.

When the grant is right and it still fails

  • Recycle the application’s connections. A pooled connection keeps its security context until it is reset, so the fix can be live and invisible.
  • Check which user the statement ran as, not which login connected. EXECUTE AS in a module changes the answer.
  • Look for a DENY on a Windows group the account inherits. It beats every GRANT and it is the least visible thing in the model.
  • Confirm the object name resolved to what you think. A default schema difference between two users sends them to two different tables with the same name.
  • Check whether the permission is on the base table or on a view the user actually queries; ownership chaining covers the second only when the owners match.

Building something you can still read next year

The durable pattern is small database roles named after access patterns rather than after teams, with permissions granted at schema level and users added to roles. It survives new tables, it survives people leaving, and it makes an audit answerable in one query. Individual grants to individual users answer today’s ticket and cost more every time somebody has to work out whether one of them can safely be removed.

Every code this article covers

Code What it points at Source
229 The named permission was denied on the object, naming the database and schema. Severity 14 Microsoft Learn
230 The named permission was denied on a column of the object. The column-level form of 229. Severity 14 Microsoft Learn
262 The named permission is denied in the named database. The database-scoped form, such as a missing CREATE right. Severity 16 Microsoft Learn
297 The user does not have permission to perform this action. No further detail is published or returned. Severity 16 Microsoft Learn
300 The named permission was denied on the object in the named database. This is the form a VIEW SERVER STATE refusal takes, where the object named is the server. Severity 14 Microsoft Learn
15247 User does not have permission to perform this action. Severity 16, with no restriction to system procedures Microsoft Learn

Confirm the fix worked

  1. Run the failing statement inside EXECUTE AS USER = '<user>'; and confirm it succeeds, then REVERT.
  2. Confirm the grant landed where you intended by querying sys.database_permissions for that principal.
  3. HAS_PERMS_BY_NAME on the object returns 1 for the permission in question.
  4. Have the application reconnect and run its real workload, because pooled connections keep the old security context until they are recycled.
  5. Confirm no new DENY was introduced by the change, by re-running the DENY query above.

Questions people ask about this

Why not just add the account to db_owner and move on?

Because you will never take it away again, and because it grants the ability to alter and drop everything in the database, not just read one table. If the application only needs to read a schema, grant that.

Does fixing this require a particular edition?

No. The permission model is identical across editions, including the free ones. This costs nothing to fix and no licence changes what a principal may do.

Why does the procedure work but its dynamic SQL fail?

Ownership chaining covers static statements inside a module whose owner also owns the tables. Dynamic SQL is compiled as a new batch and checked against the caller’s own permissions, so the chain does not apply.

Is error 300 a server-level error?

Not by definition. The published message has the same shape as 229: a permission denied on an object in a database. It is the form you see for VIEW SERVER STATE because the object named there is the server and the database is master. Read what the message names rather than assuming the scope from the number.

How do I find out what a user can already do?

Impersonate them and ask. EXECUTE AS USER = 'name'; followed by fn_my_permissions and HAS_PERMS_BY_NAME, then REVERT, tells you exactly what the engine will decide. Pass the right securable class: OBJECT, SCHEMA, DATABASE or SERVER.

Related error codes

Was this article helpful?

Your feedback helps us improve our documentation.

Related articles

Free Fix Error 15023 and Orphaned Users: Fixing SID Mismatches After a Restore Free Fix Error 208: Invalid Object Name – Schema, Database Context and Collation License Error Errors 14420 and 14421: Log Shipping Backup and Restore Alerts Firing Free Fix Error 53 and Error 26: SQL Server Instance Not Found on the Network
โ† Back to Knowledge Base