July 26, 2024 at 7:01 pm
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)
July 26, 2024 at 7:27 pm
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".
July 26, 2024 at 8:44 pm
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.
July 30, 2024 at 1:40 pm
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