views:

1515

answers:

2

Here’s the simplest repro case possible.

  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.

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
A: 

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 is

ALTER on table OBJECT

Your 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.

Remus Rusanu
Thanks for the response, but I don't think that's the problem. I just retried the whole process on a different instance and server, this time using SQL Server 2008. I got the exact same error. (I am using SSMS 2008 to connect to both instances.)
Rob Garrison
Also understand that I am trying to open the "Tables" folder, not the extended properties of a particular table.
Rob Garrison
Regarding "Your login test should have this permission as owner of the test table (it is the owner, right?).": the test SQL login is not the owner of the table. The database, login, and table were all created by a login with significantly higher privileges.
Rob Garrison
Answers to specific questions in the reply: I have not dropped any of the '##' certificates from master in either of these instances. I have not manually altered any object in the resource database.
Rob Garrison
Viewing extended properties is not granted by SELECT permission. Though SSMS should not just Throw an error in you face like that. I need to look at a Server when I get my hands on one, right now I'm just taping on my phone...
Remus Rusanu
Thanks Remus. See my comment on the question about the trace. It shows exactly what the query is that causes the error.
Rob Garrison
Resolution:Remus Rusanu was on the right track. I had been working through removing all the public permissions from our application databases. As part of that analysis, I revoked a whole list of public permissions on 119 "[sys]." objects (e.g., [sys].[sysprotects], [sys].[schemas]).I connected to an untouched SQL 2005 instance and reran this whole test. That time, there was no error. I have not narrowed it down to the exact permission that made it fail, but re-granting that set of permissions to public made the problem go away. Still not happy with the public permissions, but it's working.
Rob Garrison
Also see a related entry at connect.microsoft.com: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=520750
Rob Garrison
A: 

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)

Eric Weir