Technical Article

Delete SQL Agent Jobs owned by specific user

,

I have a SQL Server 2008 to administer and I found out that there were many sql agent jobs that were no supposed to run and that were generating errors each time they fired. The common thread amongst the agent jobs were they were owned by a domain account that was no longer valid. The agent jobs all had GUIDs for the name so viewing the list of jobs was not very helpful. I wrote this script so I could first view the agent jobs that I wanted to delete, then I could uncomment line 32 and comment out line 33 when I wanted to actually call the stored procedure that purged the jobs. You enter the user's name you want to view/delete jobs for on line 12 and control the view/delete process on lines 32 and 33.

USE msdb
GO

DECLARE @jobs_to_delete AS TABLE
(id INT IDENTITY, job_name NVARCHAR(1000));
DECLARE @i AS INT;
DECLARE @rows AS INT;
DECLARE @jobname_to_delete AS NVARCHAR(100);
DECLARE @user_name AS NVARCHAR(100);

/*insert user name below*/SET @user_name = N'';

INSERT INTO @jobs_to_delete
(job_name)
SELECT sjv.name 
FROM dbo.sysjobs_view sjv
JOIN sys.database_principals dp
ON sjv.owner_sid = dp.sid
WHERE dp.name = @user_name;

SELECT @rows = COUNT(*) FROM @jobs_to_delete;

SET @i = 1;

WHILE @i <= @rows
BEGIN
 SELECT @jobname_to_delete = job_name FROM @jobs_to_delete WHERE id = @i;
 SET @i = @i + 1;
 
 /* view the commands or delete the jobs */ --EXEC sp_delete_job @job_name = @jobname_to_delete;
 SELECT 'sp_delete_job @job_name ' + @jobname_to_delete;
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating