SQL Server 2005/2008 - 为什么没有架构名称的用户可以使用 sys.sysobjects 视图? [英] SQL Server 2005/2008 - Why is the sys.sysobjects view available to users without the schema name?

问题描述

我注意到 SQL Server Express 2008 中有一些奇怪的行为，我一直在努力寻找原因.创建新数据库时，无需指定架构标识符即可访问 sys.sysobjects 视图，如下所示:

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]

即使您的默认架构不是sys"，您也可以这样做.我对schema的理解是，数据库引擎会先在用户的默认schema中搜索一个对象，如果用户有权限再去搜索dbo schema，然后停止查找.此外，我知道可以访问 sys.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.

引擎在检查dbo"模式后是否继续寻找对象，sysobjects 视图是一个特例，还是完全不同的东西?

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?

推荐答案

这是一个特例.这些是向后兼容性视图.

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

在 SQL 2000 中，没有必要使用 sys 模式，所以他们做了它，这样现有的代码仍然可以用于这些对象.

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.

这些都已被新的系统视图和功能所取代，并被指定为 在 SQL Server 的未来版本中被删除，因此应尽可能避免(请参阅主题将系统表映射到系统视图" 在 BOL 中了解详细信息).虽然有某些情况 其中它们仍然是必需的.

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.

这篇关于SQL Server 2005/2008 - 为什么没有架构名称的用户可以使用 sys.sysobjects 视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！