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.
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;
- Read the message. It names the permission, the object, the database and the schema. That is your target, not the database as a whole.
- 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.
- 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]; - 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.
- Create a role for the access pattern rather than granting to the user:
CREATE ROLE app_read; - Grant what the application needs, at schema level if it reads a whole area:
GRANT SELECT ON SCHEMA::Sales TO app_read; - Add the user:
ALTER ROLE app_read ADD MEMBER [appuser]; - 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.
- List explicit permissions in the database and look for DENY entries covering the object, the schema or the principal.
- Check every group the login belongs to; a DENY on one inherited group beats grants on all the others.
- 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.
- 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.
- Sign the module with a certificate and grant the permission to the certificate’s principal, so the elevation is scoped to that one procedure.
- Use
EXECUTE AS OWNERonly 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.
- Grant VIEW DEFINITION where the user legitimately needs the object’s metadata.
- Check the name is not resolving against the wrong schema; an unqualified name resolves against the user’s default schema first, then dbo.
- 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.
- Grant the minimum that works:
GRANT VIEW SERVER STATE TO [monitor]; - For read-only inspection of definitions across the instance, consider
GRANT VIEW ANY DEFINITION TO [monitor]; - 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
- Run the failing statement inside
EXECUTE AS USER = '<user>';and confirm it succeeds, then REVERT. - Confirm the grant landed where you intended by querying
sys.database_permissionsfor that principal. HAS_PERMS_BY_NAMEon the object returns 1 for the permission in question.- Have the application reconnect and run its real workload, because pooled connections keep the old security context until they are recycled.
- 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.
