Sometimes we need to check who execute the command like DROP\CREATE\ALTER on Databases, also wanted to know by which Login Account, on which Date and Time they have executed the command. We can check this by enabling the Audit or from Schema change History from Reports in SSMS . Sometimes schema change may shows the report history or may not show any history of reports and Auditing was not Enabled then we will get struck at that time .
We have a default trace files on Log Folder Location , these default trace files were created at the time of SQL Installation, these will capture the DDL commands what are executed on databases. Below is the script to check the DDL command executed on Databases.
declare @d1 datetime; declare @diff int; declare @curr_tracefilename varchar(500); declare @base_tracefilename varchar(500); declare @indx int ; --shw. didn't show new user login or roles..or deleting declare @temp_trace table ( obj_name nvarchar(256) collate database_default , database_name nvarchar(256) collate database_default , start_time datetime , event_class int , event_subclass int , object_type int , server_name nvarchar(256) collate database_default , login_name nvarchar(256) collate database_default , application_name nvarchar(256) collate database_default , ddl_operation nvarchar(40) collate database_default ); select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename) select @indx = PATINDEX('%\%', @curr_tracefilename) set @curr_tracefilename = reverse(@curr_tracefilename) set @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; insert into @temp_trace select ObjectName , DatabaseName , StartTime , EventClass , EventSubClass , ObjectType , ServerName , LoginName , ApplicationName , 'temp' from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2 update @temp_trace set ddl_operation = 'CREATE' where event_class = 46 update @temp_trace set ddl_operation = 'DROP' where event_class = 47 update @temp_trace set ddl_operation = 'ALTER' where event_class = 164 select @d1 = min(start_time) from @temp_trace set @diff= datediff(hh,@d1,getdate()) set @diff=@diff/24; select @diff as difference , @d1 as date , object_type as obj_type_desc , * from @temp_trace where object_type not in (21587) order by start_time desc
Below is the sample output :