Identify the culprit who dropped or altered tables,procedures or databases in SQL Server

---

We can be find out who made DDL Changes in SQL Server database through the sys.traces dynamic management view . sys.traces DMV’s provides you details about below points:-

• who dropped tables in a database
• who altered tables in database
• who created table in database
• who created database
• who altered or changes the stored procedure
• who dropped or altered the schema

---

Firstly we are going to find the list of Trace event details in SQL Server and with the help of event id proceed further:-

---

SELECT DISTINCT
e.trace_event_id ,
e.name
FROM sys.fn_trace_geteventinfo (1) fgt
JOIN sys.trace_events e
ON fgt.eventID = e.trace_event_id

 

---

Above output shown different-different events like Object:Altered, Object:Created and Object:Deleted.
With the help of event id we can find out who had dropped, altered or created database object in SQL Server.
I have used event class id 46,47 and 164 to track database Object:Altered, Object:Created and Object:Deleted

---

Find the below query to track who dropped or created or altered database object in SQL Server :-

 

DECLARE @File_Name NVARCHAR(250)
SELECT @File_Name = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'FROM sys.traces 
WHERE is_default = 1 ;
SELECT loginname ,hostname ,applicationname ,
databasename ,objectName ,starttime ,e.name AS EventName ,databaseid
FROM sys.fn_trace_gettable(@File_Name, DEFAULT) AS tgt 
INNER JOIN sys.trace_events e 
ON tgt.EventClass = e.trace_event_id 
WHERE ( tgt.EventClass = 47 OR tgt.EventClass = 164 or tgt.EventClass=46) 
AND tgt.EventSubClass = 0 order by StartTime desc

 

After execution of above query you will get the output like below:-

 

---