How to Find out who has Modified or created object in SQL Server - SQL Server DBA Tutorial

In this video you will learn multiple ways to find out who has created or deleted objects in SQL Server as well as SQL Server database. It explains finding out who deleted or created object using SQL Server Default trace, creating extended Event and creating Server Level Audit trace to extract the object audit information in SQL Server.

--Script:
--How to find out who has modified or Created Object in SQL Server

exec sp_executesql @stmt=N'begin try
declare @enable int
select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''
if @enable = 1 --default trace is enabled
begin 
        declare @d1 datetime;
        declare @diff int;
        declare @curr_tracefilename varchar(500); 
        declare @base_tracefilename varchar(500); 
        declare @indx int ;
        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
end 
else 
begin 
        select top 0 1 as difference, 1 as date, 1 as obj_type_desc, 1 as obj_name, 1 as 
dadabase_name, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as application_name, 1 as ddl_operation 
end
end try
begin catch
select -100 as difference
,       ERROR_NUMBER() as date
,       ERROR_SEVERITY() as obj_type_desc
,       ERROR_STATE() as obj_name
,       ERROR_MESSAGE() as database_name
,       1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as 
server_name, 1 as login_name, 1 as application_name, 1 as ddl_operation 
end catch',@params=N''



  How to Find out who has Modified or created object in SQL Server

3 comments:

  1. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete
  2. hi, how to filter for certain db name ?

    ReplyDelete