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

user_62186 0
Viewed 159911

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?

sysobjects dotnetnuke permissions sql-server sql
8 Answers

Execute this code on a good server which will provide you the complete rights for PUBLIC role. Copy the output and paste to the server with the issue. Execute. Try logging in again. It fixed our problem.

SELECT  SDP.state_desc ,
        SDP.permission_name ,
        SSU.[name] AS "Schema" ,
        SSO.[name] ,
        SSO.[type]
FROM    sys.sysobjects SSO
        INNER JOIN sys.database_permissions SDP ON SSO.id = SDP.major_id
        INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid
ORDER BY SSU.[name] ,
        SSO.[name]
user_5800600

I solved this by referring properties of login user under the security, logins. then go to User Mapping and select the database then check db_datareader and db_dataweriter options.

user_68745310

In my case, my site was hosted on shared hosting and there was a resource over usage not even relating to my database, thus my database was locked down, the hosting panel was Plesk

user_8872770

In my case, simply giving the user permissions on the database fixed it.

So Right click on the database -> Click Properties -> [left hand menu] Click Permissions -> and scroll down to Backup database -> Tick "Grant"

user_148341170
Related