SSMS 中的权限问题：“对象‘extended_properties’、数据库‘mssqlsystem_resource’的 SELECT 权限被拒绝，...错误 229)”
这是最简单的重现案例。
- 创建一个全新的数据库。 （我使用的是 SQL 2005。）
- 在新数据库中创建登录名、SQL 用户和表（请参阅下面的示例代码）。
- 启动 SSMS 并打开对象资源管理器，以作为新创建的用户登录。
- 尝试在对象资源管理器中打开“Tables”文件夹。
问题
消息文本：
标题：Microsoft SQL Server Management Studio
无法检索此请求的数据。 (Microsoft.SqlServer.Management.Sdk.Sfc)
如需帮助，请单击：链接
附加信息：
执行 Transact-SQL 语句或批处理时发生异常。 (Microsoft.SqlServer.ConnectionInfo)
对对象“extended_properties”、数据库“mssqlsystemresource”、架构“sys”的 SELECT 权限被拒绝。 （Microsoft SQL Server，错误：229）
如需帮助，请单击：链接
该用户可以访问该表以及表中的记录。但用户无法访问对象资源管理器中的表列表。
SELECT USER_NAME() AS CurrentUser, col1
FROM dbo.TestTable
CurrentUser col1
----------- ----
robg_test 1000
我发现的唯一解决方法是给予用户高于必要的权限（例如 db_datareader）。
问题：
允许该用户在对象资源管理器中打开表列表所需的最低权限是什么？
我尝试授予用户对 dbo 架构的各种权限，但这没有帮助。
另请注意，我使用 SQL 用户只是为了说明问题。最初的问题出在 AD 用户身上。
此处 是 serverfault 中一个相对相似的问题。
代码
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'robg_test')
DROP LOGIN [robg_test]
GO
CREATE LOGIN [robg_test]
WITH
PASSWORD = N'CLK63!!black',
DEFAULT_DATABASE = [RGTest],
DEFAULT_LANGUAGE = [us_english],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'RGTest')
DROP DATABASE [RGTest]
GO
CREATE DATABASE [RGTest]
GO
USE [RGTest]
GO
CREATE USER [robg_test] FOR LOGIN [robg_test] WITH DEFAULT_SCHEMA = [dbo]
GO
CREATE TABLE dbo.TestTable (col1 int)
GO
GRANT SELECT ON dbo.TestTable TO [robg_test]
GO
INSERT INTO dbo.TestTable VALUES (1000)
GO
Here’s the simplest repro case possible.
- Create a brand new database. (I'm using SQL 2005.)
- Create a login, a SQL user, and a table in the new database (see sample code below).
- Launch SSMS and open Object Explorer, logging in as the newly-created user.
- Attempt to open the "Tables" folder in the Object Explorer.
The Problem
Fails with this error message.
Message Text:
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: link
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The SELECT permission was denied on the object 'extended_properties', database mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
For help, click: link
This user can access the table and the record in the table. But the user cannot access the list of tables in Object Explorer.
SELECT USER_NAME() AS CurrentUser, col1
FROM dbo.TestTable
CurrentUser col1
----------- ----
robg_test 1000
The only work-around I have found is to give the user higher-than-necessary privileges (like db_datareader).
The Question:
What is the minimum privilege required to allow this user to open the table list in Object Explorer?
I have tried granting the user various privileges on the dbo schema, but that did not help.
Note also that I am using a SQL user simply to illustrate the problem. The original problem was with an AD user.
Here is a relatively similar question at serverfault.
Code
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'robg_test')
DROP LOGIN [robg_test]
GO
CREATE LOGIN [robg_test]
WITH
PASSWORD = N'CLK63!!black',
DEFAULT_DATABASE = [RGTest],
DEFAULT_LANGUAGE = [us_english],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'RGTest')
DROP DATABASE [RGTest]
GO
CREATE DATABASE [RGTest]
GO
USE [RGTest]
GO
CREATE USER [robg_test] FOR LOGIN [robg_test] WITH DEFAULT_SCHEMA = [dbo]
GO
CREATE TABLE dbo.TestTable (col1 int)
GO
GRANT SELECT ON dbo.TestTable TO [robg_test]
GO
INSERT INTO dbo.TestTable VALUES (1000)
GO
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我有类似的问题。我通过将用户添加到公共角色来解决它。但如果您不想这样做，我还发现可以通过向用户授予视图 sys.extended-properties 的权限（在您尝试访问的数据库内的系统视图中）来解决此问题
I had a similar problem. I resolved it by adding the user to the public role. But if you didn't want to do that, I also found that it could be resolved by giving the user permission to the view sys.extended-properties (in System Views within the database that you're trying to access)
“通过使用帐户创建 SQL Server 数据库，该帐户就是所有者并拥有所有所需的访问权限”
无需进一步增强权限。
这种方法消除了该线程似乎涉及的访问错误。
我在 SSMS 以及 Visual Studio (EF) 中遇到访问错误，使用 Windows 身份验证并使用管理员帐户创建 SQL Server DB。
对我来说实用的解决方案是：
SSMS >以管理员身份启动，sql server 登录：使用 Windows 身份验证
- 不创建 SQL Server 数据库
- 但要授予帐户“master”上的“create db any”权限，
然后使用该帐户（在 master 上具有“create db any”权限）登录 SSMS
- 创建（空）数据库
（VISUAL STUDIO xtra：
然后，在 Visual Studio 中，使用该帐户连接到 sql server，并比较 LocalDB（源）和 sql server db（目标）之间的架构。效果很好：目标数据库获取架构和数据内容）
"By creating the SQL server db with an account, that account is owner and has all needed access"
No need for further enhancements in permissions.
This approach removed the access error which this thread seems about.
I encountered the access error in SSMS, as well as Visual Studio (EF), using windows authentication and having created the SQL server DB with the Administrator account.
Practical solution for me was :
SSMS > start as administrator, sql server logon : with windows authentication
- NOT to create an SQL server db
- but to give an account 'create db any' permission on 'master'
then SSMS logon with that account (that has 'create db any' permissions on master)
- to create the (empty) database
(VISUAL STUDIO xtra :
then, in visual studio, connect to sql server with that account and compare schema's between LocalDB (source) and sql server db (target). works out well : the target db gets the schema and data content)
只需通过管理员登录即可解决我的问题。例如。右键单击 SSMS 图标 ->单击以管理员身份运行-->在对象导航器中，一旦您登录（例如 Windows 登录）附加数据库（例如 Adventure Works 2022），它就会成功附加。如果您在没有管理员的情况下正常登录，则会看到以下内容。无法检索此请求的数据。 （Microsoft.SqlServer.Management.Sdk.Sfc
It got resolved for my by just logging via adminstrator login. Eg. right click SSMS icon -> Click Run as administrator --> In object navigator once you login (say windows login) attach a Database (eg. Adventure works 2022), it gets attached successfully. If you normal login without Admin the below is seeen. Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc
请检查您是否未选中
db_denydatareader数据库角色。通过删除该检查，它对我有用。Please check that you didn't check
db_denydatareaderDB role. By removing that check it worked for me.我遇到了类似的问题，并通过删除该用户的两个角色 db_denydatareader 和 db_denydatawriter 并添加其他角色来解决该问题。我用的是sql管理工作室。
I had similar problem and resolved that by removing two roles db_denydatareader and db_denydatawriter for that user and add other roles. I used sql management studio.
SSMS 尝试使用
fn_listextendedproperty。根据 MSDN 查看表的扩展属性所需的权限是您的登录测试应该具有作为测试表所有者的此权限（它是所有者，对吗？）。但即使您没有表的权限，扩展属性的查询也应该返回空结果集，而不是拒绝访问。事实上，您在资源数据库中的 sys 对象上收到访问被拒绝错误，这表明系统资源数据库 (mssqlsystemresource) 的代码签名已损坏。您是否丢失了 master 的任何“##”证书？您是否手动更改了资源数据库中的任何对象？
无论如何，此时您的实例看起来像是损坏的，我建议您联系产品支持以了解如何将其恢复到一致状态。
SSMS tries to get the extended properties of the table using
fn_listextendedproperty. According to MSDN the required permisions to view a table's extended properties isYour login test should have this permsision as owner of the test table (it is the owner, right?). But even if you don't have permissions on the table, the query for extended properties should return emtpy result set, not access denied. The fact that you get an access denied error on a sys object in the resource database indicates that the code signing of the system resource database (mssqlsystemresource) is broken. Did you drop any of the '##' certificates from master? did you manually altered any object in the resource database?
Anyway, you have a what looks like a corrupted instance at this moment and I'd recommend you contact product support on how to get it back into a coherent state.