Error 208: Invalid Object Name – Schema, Database Context and Collation

Fix it now

Error 208 is “Invalid object name” and it very rarely means the table has been dropped. Far more often the session is in a different database than you think, the caller’s default schema is not dbo, the collation makes names case-sensitive, or the caller cannot see the object at all. Qualifying names with the schema removes most of it in one move.

SELECT DB_NAME() AS CurrentDb, SCHEMA_NAME() AS DefaultSchema,
       SUSER_SNAME() AS Login, USER_NAME() AS DbUser;
SELECT OBJECT_ID('dbo.YourTable') AS ResolvesTo;
SELECT s.name AS SchemaName, o.name AS ObjectName, o.type_desc
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.name LIKE '%YourTable%';

1. Read the first result. If DB_NAME() is master or tempdb, that is the whole answer and a USE or a corrected initial catalog fixes it.
2. A NULL from OBJECT_ID means this session cannot resolve the name – which is the same conclusion the query reached, and includes the case where the caller has no permission on it.
3. Qualify every reference with its schema. dbo.YourTable resolves identically for every caller; an unqualified name does not.
4. If the object is in another database, use three parts. Across servers, use four and check the linked server exists before blaming the name.
5. If it is a temporary table, confirm it was created in the same session and has not gone out of scope.

Run this as the account that failed, using EXECUTE AS USER = 'AppUser'; and REVERT; if you cannot log in as it. Metadata is filtered by permission, so your own session is not a fair test.

If the name resolves for the account that failed, you are done. If not, the next section explains how the engine turns a name into an object.

Why it happens

An unqualified name is not a complete address. When the engine sees YourTable, it looks first in the default schema of the database user running the statement, and then in dbo. That means the same query can succeed for one login and fail for another purely because their users were created with different default schemas. The failure is deterministic and repeatable, which is what makes it so confusing when only some people report it.

Resolution is also scoped to the current database. A job step, a connection string or an application that opens on master and never issues a USE fails on every unqualified name, even though the objects plainly exist somewhere on the instance. The tool you are testing in almost always has the right database selected, which is why the same statement works interactively and fails in production.

Then there is visibility. Microsoft documents metadata visibility as limited to securables a user owns or has been granted some permission on: a catalogue query for an object the caller has no rights to returns an empty result set rather than an error. The same rule reaches the built-in functions, and the OBJECT_ID documentation says so explicitly – a metadata-emitting function such as OBJECT_ID can return NULL if the user has no permission on the object. That is why a diagnosis run as sysadmin proves nothing about what the application account can see.

Finally, procedures behave differently from ad hoc batches. Microsoft’s CREATE PROCEDURE reference states that a procedure can reference tables that do not yet exist, that only syntax checking is performed at creation time, and that objects are resolved when the procedure is first executed. So a procedure that references a missing table is created quite happily and fails later – by design, so that procedures can be created in any order.

Full reference

Symptom to cause

The four-part address, in full

Testing as somebody else

EXECUTE AS USER = 'AppUser';

SELECT DB_NAME() AS CurrentDb, SCHEMA_NAME() AS DefaultSchema, USER_NAME() AS DbUser;
SELECT OBJECT_ID('dbo.YourTable') AS ResolvesTo;
SELECT TOP 1 * FROM dbo.YourTable;

REVERT;

Because metadata is filtered by permission, this block is the difference between guessing and knowing. A NULL from OBJECT_ID inside the impersonation, where your own session returns a value, tells you the account cannot see the object – and the fix is a grant, not a rename. Remember EXECUTE AS also switches the default schema to that user’s, which is convenient here and a trap in production code that assumes otherwise.

Deferred name resolution, precisely

Microsoft’s wording is worth having exactly: a procedure can reference tables that do not yet exist; at creation time only syntax checking is performed; the procedure is not compiled until it is executed for the first time; and only during compilation are all objects referenced in the procedure resolved. So a syntactically correct procedure referencing a missing table is created successfully and fails at execution time. That is by design, so procedures can be created in any order, and it is why a deployment can look clean and break on first use.

Why qualifying names is worth doing everywhere

• It removes the default schema question entirely, so behaviour stops depending on who is connected.
• It makes the statement mean the same thing in a job step, an application and your query window.
• It removes an ambiguity the engine would otherwise resolve per user, which helps plan reuse.
• It makes 208 mean what it says: the object genuinely is not there, rather than not there for you.
• It costs four characters.

When it really is missing

Once schema, database, collation and permission are all ruled out, search the instance rather than the database: SELECT s.name, o.name, o.type_desc FROM sys.objects AS o JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.name LIKE '%YourTable%'; run in each candidate database. If nothing turns up anywhere, check whether a deployment dropped it, whether a synonym is pointing at something that has gone, and whether the object was ever created outside a transaction that was later rolled back.

Every code this article covers

Confirm the fix worked

1. SELECT OBJECT_ID('dbo.YourTable'); returns a value in the failing session, not just in yours.
2. The original statement runs under the account that failed, using EXECUTE AS USER if you cannot log in as it.
3. SELECT DB_NAME(); returns the intended database in the application connection, not only in your query window.
4. Every reference in the changed code is schema-qualified.
5. If the object is reached through a view or synonym, that view has been refreshed and the synonym’s target exists.

Questions people ask about this