在SQL身份验证的查询中出现模式名称问题 [英] Issue with schema name in queries for SQL authentication
问题描述
大家好，
我们将数据库映射到新架构teamschema，并且它具有SQL身份验证。但是每当我们执行查询时，我们都面临着无效对象名称的问题，原因是错过了模式名称。使用模式名称查询正确执行。
即使对于SQL身份验证也面临同样的问题，但问题是在连接ASP.Net应用程序时。 SP没有执行/调用未提供的模式名称。
请帮助我执行没有模式名称的SQL身份验证查询的过程。其他应用程序遵循相同的方法，但不知道如何实现它。
问候，
Ramesh
我的尝试：
SQL身份验证查询中的模式名称问题
Hi All,
We mapped our database to new schema "teamschema" and it has SQL Authentication. But whenever we are executing queries, we are facing issue as 'Invalid object name', reason is missed schema name. With schema name queries are executing properly.
Same issue facing even for SQL Authentication also, but problem is while connecting with ASP.Net application. SP's are not executing/calling due schema name not provided.
Please help me the process to execute queries without schema name for SQL authentication. Same approach following for other application but don't know how to achieve it.
Regards,
Ramesh
What I have tried:
Issue with Schema name in queries for SQL Authentication
推荐答案
您的问题是，当未指定架构时，SQL Server将首先在用户名架构中查找表，然后查找dbo架构。
您可以在SSMS的对象资源管理器中为您的用户设置默认架构
Your problem is that when the schema is not specified SQL server will first look for tables in the username schema and then the dbo schema.
You can set the default schema for your users in Object Explorer of SSMS
- (expand) the relevant database
- (expand) Security
- (expand) Users (or "Logins" in some versions)
- right click on user login
- Properties
- select General (or User Mapping in some versions)
- Default Schema
- Enter [teamschema]
- Close all dialogs
或者你可以改变所有查询以完全使用合格的表格格式为
Alternatively you could change all the queries to use fully-qualified table names in the format
[database].[schema].[table]（我建议您将此信息保存在配置中以防再次更改）
当然您可以将所有架构对象转移回 [dbo] 架构，这也将解决问题
(I suggest you keep this information somewhere in configuration in case it changes again)
Of course you could just transfer all of the schema objects back to the [dbo] schema which will also solve the problem
这篇关于在SQL身份验证的查询中出现模式名称问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
