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

Pinpointing the issue

If the problem persists beyond the above fast solution, the scenario demands a more in-depth investigation. Let's rule out common pitfalls in SQL Server permissions one by one.

Getting DENY-ed by the system

In SQL Server, if both DENY and GRANT exist for an operation, guess which one wins? Surprise, it's DENY. In short, DENY behaves like that one friend who always has the last say.

But, who's the culprit?

Check out these roles: db_denydatareader and db_denydatawriter. Despite their long, intimidating names, these roles simply deny SELECT and WRITE operations. If your user is a member of either, they could be causing the issue.

Cleaning up misunderstandings

Sometimes, you might need to clear up misunderstandings with your system:

-- Side note: Does anyone else find it weird when we "revoke" denials?
-- It's like saying "I unfriend your block! Take that!"

REVOKE SELECT ON sys.sysobjects FROM db_denydatareader;


Thus, the previous GRANT can finally take effect.

Ensuring public role permissions

Check the public role permissions. They might not be throwing a party after all.

Granting explicit SELECT privileges

Grant the SELECT permission on the sys.objects view to the user account:

GRANT SELECT ON sys.objects TO YourUserName;


The aftermath of updates and scripts

Be vigilant. Those sneaky system updates or script runs sometimes fiddle around with permissions. Trust, but verify.

When you need a friend

Consider reaching out to the DotNetNuke or SQL Server user communities for advise. When the SQL gets tough, the tough get community support!