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

Question

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

krypto69

SSChampion

Points: 13604
More actions
July 26, 2024 at 7:01 pm

#4440144
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)

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

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

ScottPletcher SSC Guru Points: 101039 More actions · Answer 1

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".

frederico_fonseca SSCoach Points: 16132 More actions · Answer 2

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.

krypto69 SSChampion Points: 13604 More actions · Answer 3

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