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:-
