SQLServerF1

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

  • Home
  • SQL Server FAQ
  • SQL Server Errors
  • SQL Server Events
  • SQL Server Trainings
  • Disclaimer

Tag: someone deleted SQL Server agent job

Finding the Login Which Deleted SQL Server Agent Job

June 22, 2016 SQLServerF1 Leave a comment
SQLServerF1

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

 
  • Database Engine
  • SQL Server
  • SQL Server 2012
  • SQL Server Jobs

Polls

Which Relational Database Management System Do you Like?

View Results

Loading ... Loading ...

Categories

  • AlwaysON (13)
  • Backup/Restore (20)
  • Blocking (2)
  • Cloud (19)
  • Cluster Shared Volumes (3)
  • ColumnStore Index (1)
  • Connectivity (13)
  • Database Engine (86)
  • Database File Gorw/Shrink (4)
  • Database Mail (1)
  • Database Mirroring (2)
  • Database Snapshot (1)
  • DBCC Commands (6)
  • Encryption (3)
  • Frequently Asked Question and Answers (9)
  • General (17)
  • Import/Export (2)
  • In-Memory OLTP (3)
  • Informatica Errors (459)
  • LogShipping (5)
  • Maintenance Plans (4)
  • MYSQL Errors (168)
  • Operating System (40)
  • Other Products Errors (2,381)
  • Replication (3)
  • SAP Errors (1,662)
  • Security (14)
  • SQL Azure (14)
  • SQL Azure Errors (17)
  • SQL Server (345)
  • SQL Server 2008 R2 (15)
  • SQL Server 2012 (17)
  • SQL Server 2014 (38)
  • SQL Server 2016 (33)
  • SQL Server Analysis Services (2)
  • SQL Server Best Practices (8)
  • SQL Server Browser (2)
  • SQL Server Cluster (9)
  • SQL Server Errors (662)
  • SQL Server Indexes (1)
  • SQL Server Integration Services (1)
  • SQL Server Jobs (4)
  • SQL Server Known Issues (103)
  • SQL Server Management Studio (8)
  • SQL Server Memory (2)
  • SQL Server Monitoring (7)
  • SQL Server on Linux (2)
  • SQL Server Performance (28)
  • SQL Server Reporting Services (1)
  • SQL Server Startup (11)
  • SQL Server Waits (23)
  • SQL Server/Service Pack Installation (42)
  • Sybase Errors (106)
  • TeraData Errors (500)
  • Third-Party Tools for SQL Server (24)
  • TraceFlags (7)

Archives

  • August 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • December 2015
  • November 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014

Recents Posts

  • SQL Server Consolidation Frequently Asked Questions and Answers
  • The login is From an Untrusted Domain and Cannot be Used with Windows Authentication
  • Updates About SQL Server 2016 on Linux
  • Details about EXECSYNC Wait Type in SQL Server
  • .NET Framework execution was aborted Error in SQL Errorlog

Connect with:

December 2019
M T W T F S S
« Aug    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Recent Comments

  • Steve Smith on Auditing or Monitoring SQL Server Instance for Any Incidents
  • Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade | SQLServerF1 on SQL Server 2012 SP1 Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 537, State 3, Severity 16
  • SQL Server 2012 SP1 Upgrade Step 'msdb110_upgrade.sql' Encountered Error 537, State 3, Severity 16 | SQLServerF1 on Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade
  • Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade | SQLServerF1 on Script level upgrade for database ‘master’ failed after Applying SQL 2008 R2 SP2

RSS Related News

  • Understanding Optimizer Timeout and how Complex queries can be Affected in SQL Server October 19, 2018
    What Is Optimizer Timeout? SQL Server uses a cost-based query optimizer. Therefore, it selects a query plan with the lowest cost after it has built and examined multiple query plans. One of the objectives of the SQL Server query optimizer (QO) is to spend a "reasonable time" in query optimization as compared to query execution....
    Joseph.Pilov
  • AGLatency report tool V2.2 introduction September 8, 2018
    I wrote an article to discuss about data movement latency between AG groups: Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups Now I develop a tool to analyze AG log block movement latency between replicas and create report accordingly (this tool has been updated to Version 2.0). You can download it here: https://github.com/suyouquan/AGLatency/releases/download/v2.2/AGLatencyV2.2.zip  ...
    SimonSu
  • July 10, 2018 Windows updates cause SQL startup issues due to “TCP port is already in use” errors July 26, 2018
    We have recently become aware of a regression in one of the TCP/IP functions that manages the TCP port pool which was introduced in the July 10, 2018 Windows updates for Windows 7/Server 2008 R2 and Windows 8.1/Server 2012 R2. This regression may cause the restart of the SQL Server service to fail with the...
    Jordon Riel
  • Lesson learned from an Availability Group performance case April 5, 2018
    Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah  Problem description  One of my customers implemented a very high workload synchronous AG (Availability Group) solution and he needs 10k transactions/sec in AG databases. With the in-memory technology, this 10K/sec goal was achieved but they found a very strange behavior in transaction processing of...
    SimonSu
  • Troubleshooting SQL Server Scheduling and Yielding April 5, 2018
    Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah    Scheduling and Yielding Knowledge Recap  We all know that SQL server is a multi-threads and multi-tasks system and it has its own thread scheduling mechanism which is a small part of job of what we call SQLOS. If you are not familiar with...
    SimonSu
  • Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups April 5, 2018
    Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah Applies to: SQL Server 2014 SP2, SQL Server 2016 SP1, SQL Server 2017 RTM  In synchronous-commit mode AG nodes sometimes you may observe your transactions are pending on HADR_SYNC_COMMIT waits. HADR_SYNC_COMMIT waits indicate that SQL server is waiting for the signal from the remote...
    SimonSu
  • Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638’ March 26, 2018
    Dear all, Depending on which products were installed on the server beforehand, a SQL Server 2017 setup may fail with the following error : TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit code 1638. Note that it could happen even after SQL Server 2017...
    Guillaume Fourrat [MSFT]
  • Uniqueifier considerations and error 666 February 16, 2018
    This post is intended to shed some light around uniqueifiers and table design that rely on its usage. First a quick information about the subject. A uniqueifier (or uniquifier as reported by SQL Server internal tools) has been used in the engine for a long time (since SQL Server 7.0), and even being known to...
    Luciano Moreira
  • SQL Setup ToolSuite Introduction (3) – SQL Registry Viewer Version 2.2 January 24, 2018
    You may want to know what registry keys will be added to system for a SQL server installation. If you use some registry snapshot tool to compare the window registries change of before and after the SQL installation you will find there are 40000~60000 modifications happening. However if you study the modifications carefully you will...
    SimonSu
  • SQL Setup ToolSuite Introduction (2) – Product Browser January 24, 2018
    Oftentimes when I worked on a setup case I hope I have a simple tool to tell me the detailed installed product properties and patch information. In Windows control panel I can find all installed products and its update but it doesn't provide detailed information like the package name of a product, the product code...
    SimonSu
  • Disclaimer

Powered by WordPress and Dynamic News.