Microsoft sql server ошибка 229

Remove From My Forums

Question

Answers

Hi Sha,

It looks like the user has not the necessary permission on the database. And you could check if the he is assigned to the proper Server Role , User Mappings and the schemas he has permissions to. Finally go to ‘permissions’ in the ‘properties’ of the
database to configure the user permission.
If you want to read up on fixed database roles and which data object they give access to, please follow this link (http://msdn.microsoft.com/en-us/library/ms189121%28v=SQL.90%29.aspx).

Or you could try to run this query to grant higher permission to the user.
Use master
go
GRANT VIEW ANY DEFINITION TO user_login;
Go

Hope this helps.

Best Regards,
Peja

Please remember to click «Mark as Answer» on the post that helps you, and to click «Unmark as Answer» if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Marked as answer by
  
  Monday, June 20, 2011 8:58 AM
- Unmarked as answer by
  Peja Tao
  Tuesday, June 21, 2011 7:46 AM
- Marked as answer by
  Sha_woop
  Thursday, June 23, 2011 6:15 AM

Источник

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

Источник

Remove From My Forums

Question

Answers

Hi Sha,

It looks like the user has not the necessary permission on the database. And you could check if the he is assigned to the proper Server Role , User Mappings and the schemas he has permissions to. Finally go to ‘permissions’ in the ‘properties’ of the
database to configure the user permission.
If you want to read up on fixed database roles and which data object they give access to, please follow this link (http://msdn.microsoft.com/en-us/library/ms189121%28v=SQL.90%29.aspx).

Or you could try to run this query to grant higher permission to the user.
Use master
go
GRANT VIEW ANY DEFINITION TO user_login;
Go

Hope this helps.

Best Regards,
Peja

Please remember to click «Mark as Answer» on the post that helps you, and to click «Unmark as Answer» if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Marked as answer by
  
  Monday, June 20, 2011 8:58 AM
- Unmarked as answer by
  Peja Tao
  Tuesday, June 21, 2011 7:46 AM
- Marked as answer by
  Sha_woop
  Thursday, June 23, 2011 6:15 AM

Источник

Remove From My Forums

Question

Answers

Hi Sha,

It looks like the user has not the necessary permission on the database. And you could check if the he is assigned to the proper Server Role , User Mappings and the schemas he has permissions to. Finally go to ‘permissions’ in the ‘properties’ of the
database to configure the user permission.
If you want to read up on fixed database roles and which data object they give access to, please follow this link (http://msdn.microsoft.com/en-us/library/ms189121%28v=SQL.90%29.aspx).

Or you could try to run this query to grant higher permission to the user.
Use master
go
GRANT VIEW ANY DEFINITION TO user_login;
Go

Hope this helps.

Best Regards,
Peja

Please remember to click «Mark as Answer» on the post that helps you, and to click «Unmark as Answer» if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Marked as answer by
  
  Monday, June 20, 2011 8:58 AM
- Unmarked as answer by
  Peja Tao
  Tuesday, June 21, 2011 7:46 AM
- Marked as answer by
  Sha_woop
  Thursday, June 23, 2011 6:15 AM

Источник

I have just added a login to test an ETL process on a database and I get the error described below when connect using the account I click on the ‘Tables’ tab on the object explorer on SSMS on a database with the login set up as a user.

Failed to retrieve data for this
request (Microsoft.SqlServer.SmoEnum)

Additional Information

An exception occured while executing a
Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)

The SELECT permissions was denied on
the object ‘extended_properties’
database ‘mssqlsystemresource’, schema
‘sys’. (Microsoft SQL Server, Error:
229).

This looks like it should be a noddy mistake but I can’t find anything that fixes the problem in a quick google search. Any ideas?

Alex Angas

2,0072 gold badges26 silver badges37 bronze badges

asked Jul 21, 2009 at 16:16

Might not be the same thing but I was getting that error today.

Turned out that in my haste to grant a user full access to a database I ticked all the boxes under role members including db_denydatareader, db_denydatawriter.

answered Mar 10, 2011 at 10:35