All geek questions in one place

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

I noticed strange behavior in SQL Server Express 2008, and I was trying to identify the cause. When you create a new database, you can access the sys.sysobjects view without specifying a schema identifier as follows:

SELECT * FROM [sysobjects]

You can 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, and then look for the dbo schema if the user has permission, and then stop looking. In addition, I know that the sys.sysobjects view can be accessed, because by default a permission is granted for the public role to choose from. I just don't know why it can be accessed without a schema name.

Does the engine continue to search for an object after checking the "dbo" schema, is sysobjects a special case scan, or is it something else completely?

+2
source share
2 answers

This is a special case. These are backward compatibility representations.

In SQL 2000, there is no need to use the sys schema to make it so that existing code works for these objects anyway.

All of them have been replaced by new system views and functions and declared that will be removed in a future version of SQL Server, so it should be (see the "Mapping System Tables to System Views" section in BOL for more details). Although there are some circumstances , in which they are still required.

+1
source

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

  • sys circuit
  • user scheme (this differs from stored procedures - it will look in the procs string scheme, and not in the user scheme.
  • dbo circuit
0
source

Source: https://habr.com/ru/post/1384319/

More articles:


All Articles