The SELECT permission was denied on the object 'all_columns', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

[zoltix]

Hi,
I have a problem with one of my databases. I think the normal
comportment of the database, when I add a user as "public" in the
security in sql engine (Root) and I map it as public in the
database.
In "Microsoft Sql Server Management Studio", I should see the objects
and the columns definition (the normal comportment).
I have strange comportment in one of my databases. in MSSMS, I can see
the list of objects(SP, Tables , View) but I can't see columns’
definition and I received a error messages.

The SELECT permission was denied on the object 'all columns', database
'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error:
229)

How can I resolve that?

In fact, I'm migrating some databases to sqlserver 2008, but I have
the same comportment in old system (2005). This database is very
old, this database from SQL 7 -> 2005 and now ->2008

Thank you for your Help

I have this problem with SQL SERVER 2005 and 2008 64 bit Enterprise
edition on Windows 2003 and Windows 2008. The size of databases is
more 500 Giga + log.

[zoltix]

On 7 déc, 15:01, zoltix <zol...@gmail.com> wrote:
> J'ai enlevé l'owner de certain user dans la base de donnée, et je leur
> ai donnée les droit minium qu'il leur fallait.
> Mais j'ai un souci dans MSSMS lorsqu'ils essayent de voire la liste
> des champs ils reçoivent cette erreur.
>
> The SELECT permission was denied on the object 'all_columns', database

> 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error:
> 229)
>

> Comment puis-je faire pour leur donner les permissions pour voir cette
> liste de colonnes.

I found the solution

In fact, there are missing default value in schema public.

use WDS_CRM
Go
EXEC sp_helprotect @name = 'all_columns'

use cdbprodV2
Go
EXEC sp_helprotect @name = 'all_columns'

So, Just add the good grant

GRANT SELECT ON sys.all_columns TO public;µ

a+