3

Is there any way to find out machine IP or machine name from where a particular stored procedure is executed in SQL Server 2005?

We have a client server architecture with .NET as a front end and SQL Server 2005 as back end. Someone in production environment has executed one stored procedure & deleted critical data from .NET application. We are unable to deduce who has done this.

We know the exact time when this has happened. Is there any way we can find the all the connection in SQL Server at that time, so that we can find out machine name from logged connection?

  • 2
    You would need to have been tracing this in some way. SQL Server doesn't log details about individual stored procedure calls. – Aaron Bertrand Mar 25 '15 at 16:40
2

If the database is in full recovery mode and you are doing periodic log backups then you can find the login that deleted the data, and the exact moment the data was deleted. Read How to read and interpret the SQL Server log. Also, with a proper backup strategy in place, it will be trivial to recover the deleted data as well.

On the other hand, if you do not have a proper backup strategy in place, then you well deserve to loose this data and more.

1

One option would be if you had a server side trace running while that incident happened or you have to dig into the transaction logs using fn_dblog (this is undocumented and unsupported).

Below query might help if the data is still persistent in DMV:

--- who did what ??
 SELECT cr.DatabaseName
    ,s.session_id
    ,s.host_name
    ,s.program_name
    ,s.client_interface_name
    ,s.login_name
    ,s.login_time
    ,s.nt_domain
    ,s.nt_user_name
    ,c.client_net_address
    ,c.local_net_address
    ,cr.ObjName
    ,cr.Query
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
    ) cr
where s.nt_user_name <> '' 
and s.session_id <> @@SPID
ORDER BY c.session_id

Update: Aaron is right - Created/Deleted/Altered <> Executed. I missed it. The default trace will log the objects that are altered, created or deleted.

  • 2
    Created/Deleted/Altered <> Executed. – Aaron Bertrand Mar 25 '15 at 16:39
  • @AaronBertrand Updated my answer... completely missed the point. Thanks for your comment. The OP can still find out if the DMV data is persisted or a server side trace was running. – Kin Shah Mar 25 '15 at 16:58
  • unfortunately server side trace was not running at that time. – user2092054 Mar 26 '15 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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