locked
Error when viewing SQL agent job RRS feed

  • Question

  • I am fighting an issue and have hit a brick wall.  I assigned a user to the SQLAgentUserRole in the MSDB database and made them owner of a job. When they login, they can see the job but when they "view history" they get.

    "The SELECT permission was denied on the object 'dm_exec_sessions', database 'mssqlsystyemresource',schema 'sys.

    A few articles that I read mentioned a DENY somewhere on a system role but I am not seeing that anywhere.  We recently migrated to 2014 and they are able to see the job fine in the 08 environment.

    They are using SSMS version 17.9.1 and connecting to a sql 2014 instance on 12.0.6205.1

    Any input would be appreciated.

    Tuesday, April 23, 2019 6:33 PM

Answers

  • By default, public have SELECT permission on sys.dm_exec_sessions. But as long you don't have the permissions VIEW SERVER STATE, you can only see your own current session.

    It appears that someone has gone on a lockdown craze on your server and denied/revoke this permission. You will need to figure who that person is, and take a discussion whether this is a good idea or not. (In my opinion, it is not.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by nickswoca Thursday, April 25, 2019 5:29 PM
    Tuesday, April 23, 2019 9:26 PM

All replies

  • By default, public have SELECT permission on sys.dm_exec_sessions. But as long you don't have the permissions VIEW SERVER STATE, you can only see your own current session.

    It appears that someone has gone on a lockdown craze on your server and denied/revoke this permission. You will need to figure who that person is, and take a discussion whether this is a good idea or not. (In my opinion, it is not.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by nickswoca Thursday, April 25, 2019 5:29 PM
    Tuesday, April 23, 2019 9:26 PM
  • Hi nickswoca,
    Please check if you deny the permission of MSDB like below screenshot.
     
    Hope this help you.
    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 24, 2019 9:40 AM
  • That is exactly what I am thinking.

    I am trying to come up with a script that will help identify the PUBLIC permission set but not having much luck.

    Thursday, April 25, 2019 4:03 PM
  • SELECT * FROM master.sys.database_permissions WHERE grantee_principal_id = 0


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 25, 2019 9:45 PM