显示“无法检索表或视图的架构信息”从表中检索数据时 [英] Shows "could not retrieve schema information for table or view" when retrieving data from table

问题描述

我可以从本地成功登录并查看，编辑SQL Management Studio中的数据和表。但是当尝试从任何其他应用程序执行相同操作时，即Visual Studio，我可以成功连接..但无法从表中检索数据。



视觉它显示的工作室

< OMSDB.dbo.Users> 
无法检索表或视图的架构信息OMSDB.dbo.Users。

我尝试过：



以前我可以毫无问题地访问同一个数据库。当我更改操作系统时，我安装了SQL Server并附加了数据库...之后只会抛出此错误。

解决方案

如果我正确理解了这个问题。您的计算机上有一个SQL安装实例。替换了操作系统，安装了新版本的SQL Server，并将数据库附加到它。现在连接到SQL服务器（即成功完成）但是，当您尝试访问OMSDB数据库中的数据时，您会收到拒绝消息。



这似乎是权限问题。至少您需要授予用户帐户db_datareader。虽然如果用户登录在SQL服务器级别重新创建，但它的SID也可能与数据库用户不同步。在这种情况下，系统存储过程sp_change_users_login将需要在用户（在OMSDB数据库中）上运行。

  EXEC  sp_change_users_login '  Auto_Fix'，'  user' 

I can successfully login in and view, edit data and tables from SQL Management Studio from local. But when trying to do the same from any other application i.e. Visual Studio i can make a successful connection.. but can't retrieve data from the table.

In visual studio it shows

<OMSDB.dbo.Users>
Could not retrieve schema information for table or view OMSDB.dbo.Users.

What I have tried:

Previously i can access the same database without any problems. When i changed the OS i installed the SQL Server and attached the database... after that only this error is thrown.

解决方案

If I understand the issue correctly. You had an instance of SQL setup, on your computer. Replaced the OS, installed a fresh version of SQL Server, and attached the database to it. Now on connecting to the SQL server (That completes succesfully) however, when trying to access data in your OMSDB database, you get a denied message.

This seems to be a permissions issues. At the very least you will need to grant to the user account db_datareader. Though its also possible that if the user login was recreated at the SQL server level, its SID is out of sync with the database user. in which case the system stored procedure sp_change_users_login will need to be run on the user (in the OMSDB database).

EXEC sp_change_users_login 'Auto_Fix', 'user'

这篇关于显示“无法检索表或视图的架构信息”从表中检索数据时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！