There are many objects that are part of one SQL Server instance, which include databases, Logins, SQL Server Agent Jobs, features like MDW, etc. As a SQL Server DBA, we are responsible for managing all these objects and part of it is to ensure security of all these objects and avoid unauthorized access, as it can result in unexpected situations. For example, some unauthorized user gains access to SQL Server database and will be able to read critical data and even worse can update or delete it, which can be very dangerous. There are many different ways to secure the data starting from restricting permissions to users to using encryption at data and transport layers too. Even, after with so many restrictions, still there will be users with high privileges who can access the SQL Server instance and can further make changes.

Of of the issue we recently faced in our environment was someone deleted a SQL Server Agent job and up on checking internally no one has come forward with the responsibility. So, it was required for us to identify the login which performed the deletion of the SQL Server Agent job. Unless, there is already some monitoring in place, we may not be aware of the job being removed and will know on a later data and we may not have the backup of msdb database which has the job details and makes things more complicated. For someone to be able to delete the SQL Server Agent job, the user would require very high permissions like SysAdmin or ServerAdmin or permission on MSDB for managing jobs. If there is no prior auditing enabled on SQL Server instance, then there are very limited chances to identify the login which deleted the job. Below are some of the ways which can help identify the user who deleted the job and other details like deleted date, etc.

– SQL Server Default trace – This will store some important information, and also includes objects creation/deletion, so there are chances to find the details in default trace. But, as we know default trace gets removed after it reaches certain size and reaches certain number of files, so if we find that job was deleted after some days, then the data might have already been gone when the older default trace gets removed.
– Check if any SQL Server audit feature is enabled, which is the easiest way to find the details.
– Check MSDB tables for SysJobHistory to see if any information can be found, but mostly it is not possible, as history gets removed when job is deleted.
– If there is any monitoring tool which captures details about the jobs, then check that to see if you can find any information about job deletion.
– Check if there are any other traces running on the SQL instance for some other purpose, possible that it may have captured job deletion details too.
– Check SQL Agent history to see if you can find any information there.

If you could not find the details even after checking all the above, the most likely you will not be able to find it anymore and forget about it and recreate the jobs. Make sure to enable auditing to be able to find the details in case the issue happens again in future. If you do not have script of the job, then restore backup of msdb database as a new user database and get the job details from sysjobs, sysjobschedules, etc tables. For more convenience in future, setup a job to capture script of all instance level objects to backup share, which can be useful in cases like these where logins, jobs, etc are deleted from the SQL instance.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2015
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings