SQL Server: cannot access system tables

4.1k views Asked by At

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]
1

There are 1 answers

0
Olivier Faucheux On BEST ANSWER

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!