Sql-server – way to check which computer or IP used a particular script

logSecuritysql serversql-server-2008

As the Title suggest, is there a way to check what IP used a particular SQL Script?

The scenario is, there is a recreation script in a email and we hypothesize that somehow, that recreation script was used and the data inside the database was removed. We want to know which computer used the script. we know that when was the script was used. But somehow the user name we used is SA which can be used by other programmers as well (sorry for this).

Is there a script or a utility that can tell us the said information?

Sample of said script:

if  exists
            (
                select  *
                    from    sysobjects
                    where   id  =   object_id   (   N'dbo.DSTRNP'   )
                        and OBJECTPROPERTY  (   id, N'IsUserTable'  )   =   1
            )
    drop    table   dbo.DSTRNP
go

create  table   dbo.DSTRNP
    (
        TEST    char(12)    not null
            ,   constraint  pk_c_DSTRNP primary key clustered
            (
                TEST
            )
    )
go

Best Answer

You could try reading the default trace to view all Host Names for queries which performed an object DROP (since you mention a drop table). Default trace should contain log record about it.

SCRIPT:

This script reads events from the default trace, if it is enabled.
Then it displays events for when any object changes occured (CREATE, DROP, ALTER).

USE tempdb
GO

DECLARE @DatabaseName nvarchar(200)
SET @DatabaseName = 'Your database' -- <<<======Specify the name of the database here, else comment this line to get details of databases

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 @indx int ;
    DECLARE @curr_tracefilename varchar(500);
    DECLARE @base_tracefilename varchar(500);
    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,
                  application_name nvarchar(256) COLLATE database_default,ddl_operation nvarchar(40) COLLATE database_default,
                  SessionLoginName nvarchar(512) COLLATE database_default, LoginName nvarchar(512) COLLATE database_default, HostName nvarchar(512) 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,ApplicationName,'temp'
        ,SessionLoginName,LoginName,HostName
    FROM ::fn_trace_gettable( @base_tracefilename, default ) WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2 

    --------------------------------------------
    AND (DatabaseName = @DatabaseName OR @DatabaseName IS NULL)

    --------------------------------------------

    UPDATE @temp_trace SET ddl_operation = 'Object:Created' WHERE event_class = 46
    UPDATE @temp_trace SET ddl_operation = 'Object:Deleted' WHERE event_class = 47
    UPDATE @temp_trace SET ddl_operation = 'Object:Altered' WHERE event_class = 164

    SELECT @d1 = min(start_time) FROM @temp_trace

    SELECT start_time AS [Event Time]
        , server_name AS [Server Name]
        , database_name AS [Database Name]
        , HostName AS [Host Name]
        , application_name AS [Application Name]
        , SessionLoginName AS [Session Login Name]
        , LoginName AS [Login Name]
        , obj_name AS [Object Name]
        , ddl_operation AS [DDL Operation]
    FROM @temp_trace WHERE object_type not in (21587)
                  --AND LoginName = 'suspect login'
    ORDER BY start_time  DESC

END

Useful links:

Related Question