0

I have an MSSQL database and a "normal" user accessing it. When I try to use tables like sys.objects, I become a permission error:

select name from sys.objects
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'.

Why? The documentation says

In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.

so I think I should be allowed to use sys.objects, even if it will list only the for me visible objects...

My user have been created with

CREATE LOGIN [factoryFX20_K_user] WITH PASSWORD=N'...', DEFAULT_DATABASE=[ff20121025], DEFAULT_LANGUAGE=[English], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
EXEC sys.sp_addsrvrolemember @loginame = N'factoryFX20_K_user', @rolename = N'serveradmin'

USE [ff20121025]
CREATE USER [factoryFX20_K_user] FOR LOGIN [factoryFX20_K_user] WITH DEFAULT_SCHEMA=[factoryFX20_K_user]
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37
  • Is it possible someone might have added extra security on `sys.objects` on your box. Can you test on a different server? – Jon Egerton Oct 26 '12 at 08:44
  • Unfortunately, I don't have another 10.0 Server. And I cannot find any customizing in the security of sys.objects. – Olivier Faucheux Oct 26 '12 at 09:02
  • 1
    possible duplicate of [The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'](http://stackoverflow.com/questions/1473315/the-select-permission-was-denied-on-the-object-sysobjects-database-mssqlsyst) – Jon Egerton Oct 26 '12 at 09:19
  • You're right: same problem and solution as the link you've found. – Olivier Faucheux Oct 26 '12 at 09:32

1 Answers1

1

The problem was ideed the same as in The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys': my user had some "denying" roles.

The solution was to remove this roles of the user:

USE [ff20121025]
EXEC sp_droprolemember N'db_denydatawriter', N'factoryFX20_K_user'
EXEC sp_droprolemember N'db_denydatareader', N'factoryFX20_K_user'

Thanks Jon!

Community
  • 1
  • 1
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37