SSMS 中的权限问题:“对象 'extended_properties'、数据库 'mssqlsystem_resource'、... 错误 229)上的 SELECT 权限被拒绝"
这是最简单的重现案例.
Here’s the simplest repro case possible.
- 创建一个全新的数据库.(我使用的是 SQL 2005.)
- 在新数据库中创建一个登录名、一个 SQL 用户和一个表(参见下面的示例代码).
- 启动 SSMS 并打开对象资源管理器，以新创建的用户身份登录.
- 尝试在对象资源管理器中打开表格"文件夹.
问题
因此错误消息而失败.
消息文本:
标题:Microsoft SQL Server 管理工作室
无法检索此请求的数据.(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).
允许此用户在对象资源管理器中打开表列表所需的最低权限是多少?
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 DB 角色.通过删除该检查它对我有用.
Please check that you didn't check db_denydatareader DB role. By removing that check it worked for me.
相关文章