View works for me ...but doesn't return results for a user in SSMS but no errors

  • Hi

    I have this view to check if a job is running:

     

    SELECT job.NAME
    ,job.job_id
    ,job.originating_server
    ,activity.run_requested_date
    ,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
    FROM msdb.dbo.sysjobs_view job
    JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
    JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
    JOIN (
    SELECT MAX(agent_start_date) AS max_agent_start_date
    FROM msdb.dbo.syssessions
    ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
    WHERE run_requested_date IS NOT NULL
    AND stop_execution_date IS NULL
    AND NAME = 'MY_JOB'

     

    When I run it works great and returns the records correctly.

    I gave him execute on all the msdb sys tables, dbadmin perms (after nothing else worked) - gave him all the rights but when he runs it he never gets results. He doesn't get an error just doesn't return results that the weird thing no errors but no records.

    I even moved it to a user DB - still doesn't return results for him. I'm assuming it has something to do with perms to sys tables. But why wouldn't he get an error? It runs but he never gets results (from SSMS)

     

  • Do you mean "sysadmin" (vs. "dbadmin").  SQL is very selective about who can see jobs.  If you didn't create the job and aren't sysadmin, you don't typically see the job.  I think there is one other obscure permission you can give someone to allow them to see jobs they didn't create, but I don't remember the details off the top of my head.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • have a look at this https://www.sqlservercentral.com/forums/topic/sqlagentreaderrole-can-create-its-own-jobs-what-is-ms-thinking

    SQLAgentReaderRole would be the role to use to allow them to view jobs - but as this gives also permissions to CREATE jobs a few DENY are required where applicable.

  • ended up writing the results to a table in a different DB via a job and granted access to that table. works fine.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply