SQL Server 2005/2008 - Why is the sys.sysobjects view available to users without the schema name?

Question

I've noticed some strange behavior in SQL Server Express 2008, and I've been trying to track down the cause. When you create a new database, you are able to access the sys.sysobjects view without specifying the schema identifier, as follows:

SELECT * FROM [sysobjects]

You are able to do this even if your default schema is something other than 'sys'. My understanding of schemas is that the database engine will first search for an object in the user's default schema, then search the dbo schema if the user has permission, then stop looking. Also, I know that the sys.sysobjects view can be accessed because the public role is given select permission by default. I just don't know why it can be accessed without the schema name.

Does the engine continue looking for an object after checking the 'dbo' schema, is the sysobjects view a special case, or is it something else entirely?

Answer 1

IIRC, SQL Server looks for objects in the following order:

1. sys schema
2. users schema (This is different for Stored Procs - it will look in the stroed procs schema rather than the users schema.
3. dbo schema

Answer 2

It's a special case. These are backward compatibility views.

In SQL 2000 it was not necessary to use the sys schema so they made it so that existing code would still work for these objects.

These have all been replaced with new system views and functions and are slated to be removed in a future version of SQL Server so should be avoided where possible (see the topic "Mapping System Tables to System Views" in BOL for details). Though there are some circumstances in which they are still required.