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

Question

SETUP  SQL Server 2005  amp  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

User · Accepted Answer

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

User · Answer

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 · Answer

Since there are so many possibilities for what might be wrong   Here s another possibility to look at   I ran into something where I had set up my own roles on a database    For instance   Administrator    Manager    DataEntry    Customer   each with their own kinds of limitations   The only ones who could use it were  Manager  role or above--because they were also set up as sysadmin because they were adding users to the database  and they were highly trusted    Also  the users that were being added were Windows Domain users--using their domain credentials    Everyone with access to the database had to be on our domain  but not everyone on the domain had access to the database--and only a few of them had access to change it    Anyway  this working system suddenly stopped working and I was getting error messages similar to the above   What I ended up doing that solved it was to go through all the permissions for the  public  role in that database and add those permissions to all of the roles that I had created   I know that everyone is supposed to be in the  public  role even though you can t add them  or rather  you can  add  them  but they won t  stay added     So  in  SQL Server Management Studio   I went into my application s database  in other words  my localized names are obscured within  lt   brackets      SQL Server  - sa   Databases  Security Roles Database Roles public    Right-click on  public  and select  Properties    In the  Database Role Properties - public  dialog  select the  Securables  page   Go through the list and for each element in the list  come up with an SQL  Grant  statement to grant exactly that permission to another role   So  for instance  there is a scalar function   dbo   fn diagramobjects   on which the  public  role has  Execute  privilege   So  I added the following line   EXEC    GRANT EXECUTE ON  dbo   fn diagramobjects  TO       RoleName             Once I had done this for all the elements in the  Securables  list  I wrapped that up in a while loop on a cursor selecting through all the roles in my roles table   This explicitly granted all the permissions of the  public  role to my database roles   At that point  all my users were working again  even after I removed their  sysadmin  access--done as a temporary measure while I figured out what happened    I m sure there s a better  more elegant  way to do this by doing some kind of a query on the database objects and selecting on the public role  but after about half and hour of investigating  I wasn t figuring it out  so I just did it the brute-force method   In case it helps someone else  here s my code   CREATE PROCEDURE  dbo   GrantAccess  AS DECLARE  AppRoleName AS sysname  DECLARE AppRoleCursor CURSOR LOCAL SCROLL LOCKS FOR     SELECT AppRoleName FROM  dbo   RoleList    OPEN AppRoleCursor  FETCH NEXT FROM AppRoleCursor INTO  AppRoleName WHILE   FETCH STATUS   0 BEGIN      EXEC    GRANT EXECUTE ON  dbo   fn diagramobjects  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp alterdiagram  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp creatediagram  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp dropdiagram  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp helpdiagramdefinition  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp helpdiagrams  TO       AppRoleName               EXEC    GRANT EXECUTE ON  dbo   sp renamediagram  TO       AppRoleName                EXEC    GRANT SELECT ON  sys   all columns  TO       AppRoleName              EXEC    GRANT SELECT ON  sys   all objects  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   all parameters  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   all sql modules  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   all views  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   allocation units  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   assemblies  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   assembly files  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   assembly modules  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   assembly references  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   assembly types  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   asymmetric keys  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   certificates  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   change tracking tables  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   check constraints  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   column type usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   column xml schema collection usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   computed columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   conversation endpoints  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   conversation groups  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   conversation priorities  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   crypt properties  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   data spaces  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database audit specification details  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database audit specifications  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database files  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database permissions  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database principal aliases  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database principals  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   database role members  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   default constraints  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   destination data spaces  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   event notifications  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   events  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   extended procedures  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   extended properties  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   filegroups  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   foreign key columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   foreign keys  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext catalogs  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext index catalog usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext index columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext index fragments  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext indexes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext stoplists  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   fulltext stopwords  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   function order columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   identity columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   index columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   indexes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   internal tables  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   key constraints  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   key encryptions  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   message type xml schema collection usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   module assembly usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   numbered procedure parameters  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   numbered procedures  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   objects  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   parameter type usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   parameter xml schema collection usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   parameters  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   partition functions  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   partition parameters  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   partition range values  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   partition schemes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   partitions  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   plan guides  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   procedures  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   remote service bindings  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   routes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   schemas  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service contract message usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service contract usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service contracts  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service message types  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service queue usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   service queues  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   services  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   spatial index tessellations  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   spatial indexes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sql dependencies  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sql modules  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   stats  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   stats columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   symmetric keys  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   synonyms  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   syscolumns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   syscomments  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysconstraints  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysdepends  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysfilegroups  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysfiles  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysforeignkeys  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysfulltextcatalogs  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysindexes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysindexkeys  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysmembers  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysobjects  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   syspermissions  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysprotects  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysreferences  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   system columns  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   system objects  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   system parameters  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   system sql modules  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   system views  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   systypes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   sysusers  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   table types  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   tables  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   transmission queue  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   trigger events  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   triggers  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   type assembly usages  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   types  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   views  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml indexes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema attributes  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema collections  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema component placements  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema components  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema elements  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema facets  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema model groups  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema namespaces  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema types  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema wildcard namespaces  TO       AppRoleName               EXEC    GRANT SELECT ON  sys   xml schema wildcards  TO       AppRoleName                FETCH NEXT FROM AppRoleCursor INTO  AppRoleName END  CLOSE AppRoleCursor RETURN 0  GO   Once that is in the system  I just needed to  Exec GrantAccess  to make it work    Of course  I have a table  RoleList  which contains a  AppRoleName  field that contains the names of the database roles   So  the mystery remains  why did all my users lose their  public  role and why could I not give it back to them   Was this part of an update to SQL Server 2008 R2   Was it because I ran another script to delete each user and add them back so to refresh their connection with the domain   Well  this solves the issue for now   One last warning  you probably should check the  public  role on your system before running this to make sure there isn t something missing or wrong  here   It s always possible something is different about your system   Hope this helps someone else

User · Answer

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 · Answer

It looks like someone might have revoked the permissions on sys configurations for the public role  Or denied access to this view to this particular user  Or the user has been created after the public role was removed from the sys configurations tables   Provide SELECT permission to public user sys configurations object

User · Answer

In my case  simply giving the user permissions on the database fixed it  So Right click on the database - gt  Click Properties - gt   left hand menu  Click Permissions - gt  and scroll down to Backup database - gt  Tick  quot Grant quot

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to permissions

Examples related to dotnetnuke

Examples related to sysobjects