Using sql-server: Permissions issue in SSMS: "The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystem_resource’, … Error 229)" on newest questions tagged sql-server – Stack Overflow
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.
Fails with this error message.
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: link
An exception occurred while executing a Transact-SQL statement or batch.
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).
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.
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
Using sql-server: using-sql-server