Allowing users to start and stop SQL Server Agent Jobs
I recently received a request to allow certain users to start and stop SQL Server Agent jobs, without giving them the ability to use the SQL Server Agent tools in SQL Server Management Studio.
I created a Visual Studio .Net application that provides that ability through stored procedures that use the WITH EXECUTE AS OWNER directive.
The T-SQL code for the SQL Server prerequisites for this app are as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 |
/* Script to create objects required for the Job Control App By: Hannah Vernon Date: 2016-04-27 Notes: Compatible with SQL Server 2005+ (not tested on 2014+) */ USE msdb; GO IF OBJECT_ID('dbo.sysjobs_log') IS NOT NULL DROP TABLE dbo.sysjobs_log; GO IF OBJECT_ID('dbo.sysjobs_action_types') IS NOT NULL DROP TABLE dbo.sysjobs_action_types; GO CREATE TABLE dbo.sysjobs_action_types ( ActionTypeID INT NOT NULL CONSTRAINT PK_sysjobs_action_types PRIMARY KEY CLUSTERED , ActionDescription VARCHAR(100) NOT NULL ) ON [PRIMARY]; INSERT INTO dbo.sysjobs_action_types (ActionTypeID, ActionDescription) VALUES (1, 'Started Job') INSERT INTO dbo.sysjobs_action_types (ActionTypeID, ActionDescription) VALUES (2, 'Stopped Job'); GO CREATE TABLE dbo.sysjobs_log ( sysjobs_log_ID INT NOT NULL CONSTRAINT PK_sysjobs_log PRIMARY KEY CLUSTERED IDENTITY(1,1) , job_id UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_sysjobs_log__job_id FOREIGN KEY REFERENCES dbo.sysjobs(job_id) ON DELETE CASCADE , JobActionDateTime DATETIME NOT NULL CONSTRAINT DF_sysjobs_log_JobActionDateTime DEFAULT (GETDATE()) , ActionBy SYSNAME NOT NULL CONSTRAINT DF_sysjobs_log_StartedBy DEFAULT (ORIGINAL_LOGIN()) , ActionAs SYSNAME NOT NULL CONSTRAINT DF_sysjobs_log_StartedAs DEFAULT (SUSER_SNAME()) , ActionTypeID INT NOT NULL CONSTRAINT FK_sysjobs_log__ActionType FOREIGN KEY REFERENCES dbo.sysjobs_action_types(ActionTypeID) ) ON [PRIMARY]; GO IF OBJECT_ID('dbo.sysjobs_filter') IS NOT NULL DROP TABLE dbo.sysjobs_filter; GO CREATE TABLE dbo.sysjobs_filter ( job_id UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_sysjobs_filter__job_id FOREIGN KEY REFERENCES dbo.sysjobs(job_id) ON DELETE CASCADE ) ON [PRIMARY]; CREATE INDEX PK_sysjobs_filter ON dbo.sysjobs_filter(job_id); GO IF OBJECT_ID('dbo.cp_s_job_list') IS NOT NULL DROP PROCEDURE dbo.cp_s_job_list; GO /************************************************************ Shows a list of jobs that are available for non-DBAs to run using the dbo.cp_s_start_job interface By: Hannah Vernon Date: 2016-04-25 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_s_job_list WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SELECT JobID = sj.job_id , JobName = sj.name , JobDescription = sj.description , IsEnabled = CASE WHEN sj.enabled = 1 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END , LastRunDate = (SELECT TOP(1) msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) FROM msdb.dbo.sysjobhistory sjh WHERE sjh.job_id = sj.job_id ORDER BY sjh.run_date DESC, sjh.run_time DESC) FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobs_filter sjf ON sj.job_id = sjf.job_id ORDER BY sj.name; END GO IF OBJECT_ID('dbo.cp_s_job_status') IS NOT NULL DROP PROCEDURE dbo.cp_s_job_status; GO /************************************************************ Gets the status of the specified Job By: Hannah Vernon Date: 2016-04-25 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_s_job_status ( @JobID UNIQUEIDENTIFIER ) WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs_filter sjf WHERE sjf.job_id = @JobID) BEGIN DECLARE @can_see_all_running_jobs INT DECLARE @job_owner sysname DECLARE @job_execution_state TABLE ( job_id UNIQUEIDENTIFIER NOT NULL , date_started INT NOT NULL , time_started INT NOT NULL , execution_job_status INT NOT NULL , execution_step_id INT NULL , execution_step_name sysname COLLATE database_default NULL , execution_retry_attempt INT NOT NULL , next_run_date INT NOT NULL , next_run_time INT NOT NULL , next_run_schedule_id INT NOT NULL ); DECLARE @filtered_jobs TABLE ( job_id UNIQUEIDENTIFIER NOT NULL , date_created DATETIME NOT NULL , date_last_modified DATETIME NOT NULL , current_execution_status INT NULL , current_execution_step INT NULL , current_execution_step_name sysname COLLATE database_default NULL , current_retry_attempt INT NULL , last_run_date INT NOT NULL , last_run_time INT NOT NULL , last_run_outcome INT NOT NULL , next_run_date INT NULL , next_run_time INT NULL , next_run_schedule_id INT NULL , type INT NOT NULL ); DECLARE @xp_results TABLE ( job_id UNIQUEIDENTIFIER NOT NULL , last_run_date INT NOT NULL , last_run_time INT NOT NULL , next_run_date INT NOT NULL , next_run_time INT NOT NULL , next_run_schedule_id INT NOT NULL , requested_to_run INT NOT NULL -- BOOL , request_source INT NOT NULL , request_source_id sysname COLLATE database_default NULL , running INT NOT NULL -- BOOL , current_step INT NOT NULL , current_retry_attempt INT NOT NULL , job_state INT NOT NULL ); SET @can_see_all_running_jobs = 1; SELECT @job_owner = SUSER_SNAME(); INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @JobID; INSERT INTO @job_execution_state SELECT xpr.job_id , xpr.last_run_date , xpr.last_run_time , xpr.job_state , sjs.step_id , sjs.step_name , xpr.current_retry_attempt , xpr.next_run_date , xpr.next_run_time , xpr.next_run_schedule_id FROM @xp_results xpr LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)), msdb.dbo.sysjobs_view sjv WHERE (sjv.job_id = xpr.job_id); INSERT INTO @filtered_jobs SELECT sjv.job_id , sjv.date_created , sjv.date_modified , ISNULL(jes.execution_job_status, 4) -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE) , current_execution_step = jes.execution_step_id , current_execution_step_name = jes.execution_step_name , jes.execution_retry_attempt -- Will be NULL if the job is non-local or is not in @job_execution_state , 0 -- last_run_date placeholder , 0 -- last_run_time placeholder , 5 -- last_run_outcome placeholder , jes.next_run_date -- Will be NULL if the job is non-local or is not in @job_execution_state , jes.next_run_time -- Will be NULL if the job is non-local or is not in @job_execution_state , jes.next_run_schedule_id -- Will be NULL if the job is non-local or is not in @job_execution_state , 0 -- type placeholder FROM msdb.dbo.sysjobs_view sjv LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id) WHERE sjv.job_id = @JobID; UPDATE @filtered_jobs SET current_execution_status = NULL WHERE (current_execution_status = 4) AND (job_id IN ( SELECT job_id FROM msdb.dbo.sysjobservers WHERE (server_id <> 0)) ); UPDATE @filtered_jobs SET last_run_date = sjs.last_run_date, last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id); UPDATE @filtered_jobs SET type = 1 -- LOCAL FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id) AND (server_id = 0); UPDATE @filtered_jobs SET type = 2 -- MULTI-SERVER FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id) AND (server_id <> 0); SELECT JobID = sjv.job_id , JobName = sjv.name , JobEnabled = sjv.enabled , JobDescription = sjv.description , StartStepID = sjv.start_step_id , last_run_date_time = CASE WHEN fj.last_run_date > 0 THEN msdb.dbo.agent_datetime(fj.last_run_date, fj.last_run_time) ELSE NULL END , fj.last_run_outcome , current_execution_status = COALESCE(fj.current_execution_status, 0) , current_execution_step = COALESCE(fj.current_execution_step, 0) , current_execution_step_name = fj.current_execution_step_name , current_retry_attempt = COALESCE(fj.current_retry_attempt, 0) FROM @filtered_jobs fj LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id) ORDER BY sjv.job_id; END ELSE BEGIN DECLARE @msg NVARCHAR(1000); SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.'; RAISERROR (@msg, 11, 1); END END GO IF OBJECT_ID('dbo.cp_e_start_job') IS NOT NULL DROP PROCEDURE dbo.cp_e_start_job; GO /************************************************************ Starts the specified Job By: Hannah Vernon Date: 2016-04-25 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_e_start_job ( @JobID UNIQUEIDENTIFIER ) WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; DECLARE @msg NVARCHAR(1000); IF EXISTS ( SELECT 1 FROM dbo.sysjobs_filter sjmf INNER JOIN dbo.sysjobs sj ON sjmf.job_id = sj.job_id WHERE sjmf.job_id = @JobID ) BEGIN INSERT INTO dbo.sysjobs_log (job_id, ActionTypeID) VALUES (@JobID, 1); --1 is "Started Job" EXEC dbo.sp_start_job @job_id = @JobID; END ELSE BEGIN SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.'; RAISERROR (@msg, 11, 1); END END GO IF OBJECT_ID('dbo.cp_e_stop_job') IS NOT NULL DROP PROCEDURE dbo.cp_e_stop_job; GO /************************************************************ Stops the specified Job By: Hannah Vernon Date: 2016-04-25 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_e_stop_job ( @JobID UNIQUEIDENTIFIER ) WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; DECLARE @msg NVARCHAR(1000); IF EXISTS ( SELECT 1 FROM dbo.sysjobs_filter sjmf INNER JOIN dbo.sysjobs sj ON sjmf.job_id = sj.job_id WHERE sjmf.job_id = @JobID ) BEGIN INSERT INTO dbo.sysjobs_log (job_id, ActionTypeID) VALUES (@JobID, 2); --1 is "Started Job" EXEC dbo.sp_stop_job @job_id = @JobID; END ELSE BEGIN SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.'; RAISERROR (@msg, 11, 1); END END GO IF OBJECT_ID('dbo.cp_s_job_control_perms') IS NOT NULL DROP PROCEDURE dbo.cp_s_job_control_perms; GO /************************************************************ Confirms the objects exist and the determines the callers security level for the SQL Agent Job Control system. By: Hannah Vernon Date: 2016-04-27 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_s_job_control_perms WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; DECLARE @OutputList TABLE ( ObjectName SYSNAME NOT NULL , ObjectVal INT NOT NULL ); INSERT INTO @OutputList (ObjectName, ObjectVal) SELECT ObjectName = 'dbo.cp_s_job_list', ObjectID = COALESCE(OBJECT_ID('dbo.cp_s_job_list'), 0) UNION ALL SELECT 'dbo.cp_s_job_status', COALESCE(OBJECT_ID('dbo.cp_s_job_status'), 0) UNION ALL SELECT 'dbo.cp_e_stop_job', COALESCE(OBJECT_ID('dbo.cp_e_stop_job'), 0) UNION ALL SELECT 'dbo.cp_e_start_job', COALESCE(OBJECT_ID('dbo.cp_e_start_job'), 0) EXECUTE AS CALLER; INSERT INTO @OutputList (ObjectName, ObjectVal) SELECT 'ControlPermissions', ( SELECT COUNT(1) FROM sys.fn_my_permissions('dbo.sysjobs', 'OBJECT') p WHERE p.permission_name = 'CONTROL' ); REVERT; SELECT * FROM @OutputList; END GO IF OBJECT_ID('dbo.cp_s_get_job_outcome') IS NOT NULL DROP PROCEDURE dbo.cp_s_get_job_outcome; GO /************************************************************ Gets the most recent history item for the specified job By: Hannah Vernon Date: 2016-04-27 Version: 1.0 Initial Version ************************************************************/ CREATE PROCEDURE dbo.cp_s_get_job_outcome ( @JobID UNIQUEIDENTIFIER ) WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SELECT TOP(1) sj.job_id , [Job Name] = sj.name , [Step Name] = sjh.step_name , sjh.message FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id WHERE sj.job_id = @JobID ORDER BY msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) DESC , sjh.step_id; END GO IF NOT EXISTS ( SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'agent_job_control' ) CREATE ROLE agent_job_control AUTHORIZATION [dbo]; GRANT EXECUTE ON dbo.cp_s_job_list TO agent_job_control; GRANT EXECUTE ON dbo.cp_s_job_status TO agent_job_control; GRANT EXECUTE ON dbo.cp_e_start_job TO agent_job_control; GRANT EXECUTE ON dbo.cp_e_stop_job TO agent_job_control; GRANT EXECUTE ON dbo.cp_s_job_control_perms TO agent_job_control; GRANT EXECUTE ON dbo.cp_s_get_job_outcome TO agent_job_control; GO |
You’ll need to create user principals in the msdb object, and add them to the agent_job_control database role. Once that is completed those users can start and stop jobs, and obtain the status and recent history of jobs, by specifying the job_id uniqueidentifier.
Project Code is here: SQLServerJobControl
Awesome post, have been looking for a very long time. What version of Visual Studio is the code written. I downloaded it , but unable to run, i have VS 2019. please help
It would have been Visual Studio 2012 or 2015. I just opened the source in Visual Studio 2019 – it does compile, but the appears to be some issue with the designer for the main form. I’ll see if I can determine what the exact issue is, and get back to you!
Hi – I found the issue – there was some incorrect XML tags in the project file and the main.vb designer file. I’ve verified that you can compile, debug, and run the source code now. The fixed files are available from: https://www.sqlserverscience.com/wp-content/uploads/2020/07/SQLServerJobControl.zip (i’ve also updated the link the article above to point to the new version).
Thanks alot brother for your quick response. however, i am unable to reach the download page.
Strange – I see an error there too.. I’ll get it fixed up, and let you know when it’s working.
Thanks a lot Hannah !!!
Hi – I’ve corrected the issue.
Perfect, i can now download the updated files. This is just awesome. Thank you so much brother for your great help
One Question, so how does the security works for this app, will this app allow users authenticating through an AD security group ??
You can certainly use an Active Directory group – you’ll need to add the group to the msdb database. Once you run the app using an Active Directory login that has sysadmin on the SQL Server, you can manage users and the jobs those users can start/stop via the app.
Thanks alot my friend. This works perfectly well. Thank you so much. A few suggestions to this from a security stand point. Can this be made more granular to a User / ADGroup Job Mapping. This way a target ADGroup / User can only see the job assigned to their profile ?
Awesome. Thanks a lot for looking into this. I really appreciate your help.