What permissions might be missing on error 229 from 'tables' node on SSMS object explorer

Question

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?

Solution

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.

Comments

[MrGigu]

Do you get this error when you log in as SA (or equivalant administrator rights)?

[ConcernedOfTunbridgeWells]

No. Only when I log in as the user I just created.

[Rob Garrison]

I posted a somewhat similar question on stackoverflow here: stackoverflow.com/questions/2002773/…