SQL Server模式和默认模式

Question

我在我的数据库中定义了一个架构。但是现在每次执行SQL语句时，我都必须提供架构...

SELECT * FROM [myschema].table

我使用管理工具为我的用户设置了默认的架构，并运行了下面的代码：

ALTER USER myUser WITH DEFAULT_SCHEMA [myschema]

即使我在查询时没有指定架构（SELECT * FROM table），仍然会出现"无效的对象'table'"的错误提示。

是否有方法可以不用一直指定架构名称就能编写 SELECT * FROM table 语句呢？

我的SQL Management Studio版本是2005。

- pdiddy

---

你是从某些代码（Java，.Net）还是SQL管理器中发出SQL查询的？ - Faisal Feroz

从 SQL 管理工具中... - pdiddy

Answer 1

如果用户是一个SA，根据文档，这将不起作用，SA用户总是默认为dbo模式。

如果用户是sysadmin固定服务器角色的成员，则DEFAULT_SCHEMA的值将被忽略。所有sysadmin固定服务器角色的成员都有一个默认模式为dbo。

- Dustin Laine

---

运行这些SQL命令SELECT SUSER_NAME()和SELECT USER_NAME()，告诉我你的输出结果。 - Dustin Laine

有什么原因导致这种差异吗？ - pdiddy

这个小信息帮了我大忙——默认模式由于某些原因被忽略了，现在我们知道原因了... - codeulike

谢谢。这解决了我的问题。我在执行 SQL 脚本迁移时遇到了“对象未找到”的错误。因为我的用户是 sysadmin，所以所有的脚本都运行失败了 :) - Taher

@DustinLaine - SELECT SUSER_NAME() == MyUserForLogin, SELECT USER_NAME() = dbo。这是什么意思？这可能与我在这里遇到的问题有关吗 - http://stackoverflow.com/questions/21151693/sql-query-works-in-ssis-ssms-sql-server-but-fails-in-deployment - Steam

所以问题是......权限太多了！移除 sysadmin 角色。微软风格 :) - Chris W

Answer 2

有几个选择：

1. Is your user listed under Security > Users (in SSMS)? Check the Properties (right click the name), and see if the Default schema is set in the context of the database, rather than the instance (which is what ALTER USER is setting).

2. Create a synonym for the table you want to reference:

   CREATE SYNONYM table_name  
      FOR [your_db].[your_schema].table_name
   

   ...which will affect everyone who doesn't use at least two name notation, in the context of that database. Read more about it here. But it is associated ultimately to a schema.

3. Check that the database selected in the "Available Databases" drop down (upper left, to the left of the Execute button) is correct.

4. Use three name notation when specifying table (and view) references:

   SELECT * 
     FROM [your_db].[your_schema].table_name
   

- OMG Ponies

---

我的问题是关于在SSMS中是否可以通过某些配置避免使用名称标记...我不想在查询中包含整个数据库、架构，因为它可能很长。可用数据库已经被正确选择了。 - pdiddy

@pdiddy：我补充一下，同义词也是另一个考虑因素。抱歉，我没注意到你不想使用名称符号。 - OMG Ponies

对于选项1，我如何检查上下文？ - pdiddy

@pdiddy：它基于在SSMS中选择的Available Databases下拉列表中的数据库。运行CREATE SYNONYM语句，然后检查在SSMS中数据库下的Synonyms节点下列出了什么 - 对我来说，同义词包括模式名称。可能需要刷新同义词节点才能看到新创建的同义词。 - OMG Ponies

Answer 3

如果您不想使用“完全限定”的SQL名称，则需要避免使用未使用“dbo”默认架构分配的任何帐户或角色来创建表。如果您不打算使用它，则为什么需要更改用户的默认架构？

- Mitch Stokely

---

为什么他应该使用“全限定”SQL名称？例如，他想在数据库中存储一些用户数据。而且可能有很多用户使用同一个程序。按照模式，他可以拥有自己创建的表。在我们的程序中，这是以相同的方式进行的。唯一重要的是工作默认模式！ - Jettero