The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'

Question

SETUP: SQL Server 2005 & DotNetNuke 05.01.02.

This started with me trying to install a DNN Module that had "select * from dbo.sysobjects" in it's SQL scripts. That failed with the following error:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.

I logged into the database via SQL Server Management Studio as the DNN user account, and I get the same error when I try and perform a SELECT on the sysobjects view.

I tried to grant the DNN user account explicit SELECT permission to that view. When I check it by going to Security -> Users -> DNNUserLogin-> right-click -> Properties -> Securables and scroll down to find the sys.sysobjects view, it says this user account has explicit permissions for dbo: And the SELECT checkbox is checked. But I still cannot perform a select on the sysobjects view as that DNN user account.

What am I doing wrong? How can I make this work?

Answer 1

This was a problem with the user having deny privileges as well; in my haste to grant permissions I basically gave the user everything. And deny was killing it. So as soon as I removed those permissions it worked.

Answer 2

I had the same error and SOLVED by removing the DB roles db_denydatawriter and db_denydatreader of the DB user. For that, select the appropriate DB user on logins >> properties >> user mappings >> find out DB and select it >> uncheck the mentioned Db user roles. Thats it !!