SSMS中的权限问题:“对对象'extended_properties'，数据库'mssqlsystem_resource'，...错误229的SELECT权限被拒绝". [英] Permissions issue in SSMS: "The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystem_resource', ... Error 229)"

问题描述

这是最简单的复制案例.

Here’s the simplest repro case possible.

1. 创建一个全新的数据库. (我正在使用SQL 2005.)
2. 在新数据库中创建一个登录名，一个SQL用户和一个表(请参见下面的示例代码).
3. 启动SSMS并打开Object Explorer，以新创建的用户身份登录.
4. 尝试在对象资源管理器中打开表"文件夹.

1. Create a brand new database. (I'm using SQL 2005.)
2. Create a login, a SQL user, and a table in the new database (see sample code below).
3. Launch SSMS and open Object Explorer, logging in as the newly-created user.
4. Attempt to open the "Tables" folder in the Object Explorer.

问题

失败，出现 此错误消息 .

The Problem

Fails with this error message.

消息文本:

标题:Microsoft SQL Server Management Studio
无法检索此请求的数据. (Microsoft.SqlServer.Management.Sdk.Sfc)
要获取帮助，请单击:链接
其他信息:
执行Transact-SQL语句或批处理时发生异常. (Microsoft.SqlServer.ConnectionInfo)
对对象"extended_properties"，数据库mssqlsystemresource，模式"sys"的SELECT权限被拒绝. (Microsoft SQL Server，错误:229)
要获取帮助，请单击:链接

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

我发现的唯一解决方法是为用户提供高于必要的特权(例如db_datareader).

The only work-around I have found is to give the user higher-than-necessary privileges (like db_datareader).

允许该用户在Object Explorer中打开表列表所需的 minimum 特权是什么?

What is the minimum privilege required to allow this user to open the table list in Object Explorer?

我尝试向用户授予dbo模式的各种权限，但这无济于事.

I have tried granting the user various privileges on the dbo schema, but that did not help.

还请注意，我使用SQL用户只是为了说明问题.最初的问题是与AD用户有关.

Note also that I am using a SQL user simply to illustrate the problem. The original problem was with an AD user.

此处是serverfault中一个相对类似的问题.

Here is a relatively similar question at 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

推荐答案

请检查您是否未选中db_denydatareader数据库角色.通过删除该支票，它对我有用.

Please check that you didn't check db_denydatareader DB role. By removing that check it worked for me.

这篇关于SSMS中的权限问题:“对对象'extended_properties'，数据库'mssqlsystem_resource'，...错误229的SELECT权限被拒绝".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！