SQLServer:为用户选择默认架构时出现问题
我错误命名了一个架构,并且所有表都是使用错误名称的架构创建的。所以我创建了一个,并使用 alter schema 将表转移过来。
第一个问题是,当我尝试删除错误的架构时,SQL Server 会失败,第二个问题是,尽管用户是使用新架构默认创建的,但我想我可以简单地说
select * from table1,
而不是
select * from myschema.table1,
,但它不起作用。我必须明确。有什么想法吗? 更奇怪的是,默认使用错误架构创建的旧用户现在无法更改它。这有点令人沮丧。请帮忙。
I named a schema wrong, and all the tables are created with a schema of a wrong name. So I created a one, and use alter schema to transfer the tables over.
First problem is, SQL Server fails when I try to drop the wrong schema, second, although users were created with the new schema as default, I figure I could simply say
select * from table1,
rather than
select * from myschema.table1,
but it doesn't work. I have to be explicit. Any ideas what's going on?
The weirder thing is, the old user that was created with the wrong schema as default, now is not able to change it. It's kind of frustrating. Please help.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以尝试执行以下操作:
假设“myschema”是您希望用户默认使用的新架构。
我知道您提到新用户是使用新架构创建的,但如果是这种情况,那么您不应该看到这种错误。所以似乎出了什么问题。
显式设置默认架构可能会纠正此问题。
一般来说,
当前数据库中登录会话的默认架构是分配给当前数据库级别主体 - 数据库用户的默认架构。
如果您引用默认架构中的对象,则无需指定架构名称。
如果您引用默认架构之外的对象,则必须指定架构名称。
有关会话架构等的更多详细信息,也许这也会有所帮助
Can you try doing the following :
Assuming "myschema" is the new schema that you want the user to default to.
I know you mentioned that the new user is created with the new schema but if that is the case, then you should not see this kind of error. So something seems to have gone wrong.
Explicitly setting the default schema might correct this.
In general,
The default schema of your login session in the current database is the default schema assigned to the current database level principal - database user.
If you are referring to an object in the default schema, you do not need to specify the schema name.
If you are referring to an object outside the default schema, you must specify the schema name.
For more details regarding the schema for session etc maybe this will also help