September 16, 2015 at 7:48 pm
I'm trying to query SQL Agent Running Jobs; I've stuffed some code (found somewhere on the internet), which works fine (logged in as me/Sysadmin), into an SProc but when run from the APP I get
SELECT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'.
I have an "Execute As" UserID where I want to access data from other databases, readonly, so I added
WITH EXECUTE AS 'MyExecUser'
and gave that user db_DataReader permissions on MSDB ... same error, checked BOL DOCs and it says "Only users who are members of the sysadmin fixed server role can access this table [dbo.syssessions]"
I can't seem to find a way around that ...
... perhaps I don't need the code that references dbo.syssessions to find a currently running job, or there is "another way"? but my understanding is that an aborted session can leave the Run Request date behind (and leave [stop_execution_date] NULL), and therefore the only reliable?? way to detect "currently running" is to match the Session ID against most-recent-session-ID (indeed, if I do leave that out one of my jobs appears to have been running since 2011 :w00t: )
SELECTJ.Name,
J.job_ID,
J.Originating_Server,
A.run_requested_Date,
[Elapsed (Sec)] = DATEDIFF(SECOND, A.run_requested_Date, GetDate())
FROMmsdb.dbo.sysjobs_view AS J
JOIN msdb.dbo.sysjobactivity AS A
ON A.job_id = J.job_id
-- Check that session is "current"
JOIN msdb.dbo.syssessions AS S
ON S.session_id = A.session_id
JOIN
(
SELECTTOP 1 [max_agent_start_date] = agent_start_date
FROMmsdb.dbo.syssessions
ORDER BY agent_start_date DESC
) AS SMax
ON SMax.max_agent_start_date = S.agent_start_date
WHERE A.run_Requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
October 12, 2016 at 1:41 am
Hi, I've been working on a program to allow a non-privileged user to start jobs (s)he doesn't own.
With the correct setup, querying the tables in MSDB is possible. I set up certificate authorisation based on a post by Erland Sommarskog (standing on the shoulders of giants).
http://www.sommarskog.se/grantperm/jobstart-2012.sql
This is the setup script I used, which works on my development machine - haven't tried the production machines yet:
-- http://www.sommarskog.se/grantperm/jobstart-2012.sql
USE master
go
-- Create a test login.
--CREATE LOGIN aufbereitung WITH PASSWORD = 'CeRT=0=TeST'
--go
-- Create test database.
--CREATE DATABASE osc
--go
USE msdb
-- Create certificate in msdb.
CREATE CERTIFICATE jobstartcert
ENCRYPTION BY PASSWORD = 'Strawberry Fields Forever'
WITH SUBJECT = 'To permit starting the Testjob',
START_DATE = '20161001', EXPIRY_DATE = '20990101'
go
-- Create a user for the certificate.
CREATE USER jobstartcert_user FROM CERTIFICATE jobstartcert
go
-- Grant rights for the certificate login to run jobs.
EXEC sp_addrolemember SQLAgentOperatorRole, jobstartcert_user;
GRANT SELECT on dbo.sysschedules TO jobstartcert_user;
GRANT SELECT on dbo.sysjobschedules TO jobstartcert_user;
GRANT SELECT on dbo.sysjobhistory TO jobstartcert_user;
GRANT SELECT on dbo.sysjobactivity TO jobstartcert_user;
go
-- Counter-sign sp_start_job and its subprocedures.
ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE jobstartcert
WITH PASSWORD = 'Strawberry Fields Forever'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE jobstartcert
WITH PASSWORD = 'Strawberry Fields Forever'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE jobstartcert
WITH PASSWORD = 'Strawberry Fields Forever'
go
-- Get the certificate bytes into a temp table so we can use it in
-- the target database.
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
SELECT certencoded(cert_id('jobstartcert')),
certprivatekey(cert_id('jobstartcert'), 'Looking through a Glass Onion',
'Strawberry Fields Forever')
go
-- Move to test database.
USE Tx_Steuerung
go
-- Create a database user for the test login.
--CREATE USER aufbereitung
--go
-- Create a procedure that starts a certain job.
--CREATE PROCEDURE start_this_job AS
-- EXEC msdb..sp_start_job 'OSC_Test_Job'
--go
-- Give test user right to execute the procedure.
GRANT EXECUTE ON dbo.usp_Launch_Job TO aufbereitung;
GRANT EXECUTE ON dbo.usp_JobList TO aufbereitung;
go
-- Import the certificate we created in msdb into the test database.
DECLARE @sql nvarchar(MAX)
SELECT @sql =
'CREATE CERTIFICATE jobstartcert
FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
DECRYPTION BY PASSWORD = ''Looking through a Glass Onion'',
ENCRYPTION BY PASSWORD = ''Fixing a Hole'')'
FROM #keys
PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go
-- Delete the files.
--EXEC master..xp_cmdshell 'DEL C:\temp\jobstartcert.*', 'no_output'
--go
-- Sign the test procedures.
ADD SIGNATURE TO start_this_job BY CERTIFICATE jobstartcert
WITH PASSWORD = 'Fixing a Hole'
ADD SIGNATURE TO dbo.usp_JobList BY CERTIFICATE jobstartcert
WITH PASSWORD = 'Fixing a Hole';
go
-- Switch to the test user.
EXECUTE AS LOGIN = 'aufbereitung'
go
-- Start the job, this succeeds.
EXEC start_this_job
go
-- Back to ourselves.
REVERT
go
-- Clean up.
USE msdb
go
DROP COUNTER SIGNATURE FROM sp_sqlagent_notify
BY CERTIFICATE jobstartcert
DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers
BY CERTIFICATE jobstartcert
DROP COUNTER SIGNATURE from sp_start_job
BY CERTIFICATE jobstartcert
DROP USER jobstartcert_user
DROP CERTIFICATE jobstartcert
go
--USE master
--go
--DROP DATABASE osc
--DROP LOGIN aufbereitung
My current query of the database (encapsulated in usp_joblist) gives the same results (further testing and unpleasant surprises excepted) as the SQL Agent job activity monitor.
SELECT jv.name,
jv.job_id,
ja.start_execution_date,
ja.stop_execution_date,
jh.run_status,
ja.next_scheduled_run_date AS calcNextRun
FROM msdb.dbo.sysjobs_view jv
INNER JOIN ( SELECT job_id ,
start_execution_date ,
stop_execution_date ,
job_history_id ,
next_scheduled_run_date,
ROW_NUMBER() OVER ( PARTITION BY job_id ORDER BY COALESCE(job_history_id, 0) DESC, session_id DESC ) rn
FROM msdb.dbo.sysjobactivity) ja
ON ja.job_id = jv.job_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id = ja.job_history_id
WHERE COALESCE(ja.rn, 1) = 1
ORDER BY jv.name;
Note that the certificate login only has SQLAgentOperatorRole. This is enough to view the tables in MSDB and to start a job, but not enough to schedule a job which requires SYSAdmin rights.
I ended up writing the schedule information to a table, then starting a job to set the schedule. The job runs with an owner who has sysadmin rights.
Hope this helps.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply