1

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
2
  • 1
    After the fact? Unlikely. SQL Server doesn't keep track of all queries that run. If the user did something that is captured by the default trace, or any extended event sessions you have running, then maybe. But we'd need more details about what the script actually did, and the clock is ticking, because the default trace purges data as it ages out. Jun 28, 2018 at 3:16
  • @AaronBertrand I updated the question sir, But I tried to use the script in a database, the schema changes was not detected by SQL SERVER 2008.
    – Mr.J
    Jun 28, 2018 at 3:30

1 Answer 1

1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.