Link to home
Start Free TrialLog in
Avatar of bjbrown
bjbrownFlag for United States of America

asked on

trace sql instance

We have something or someone insterting data into our SQL server 2008 R2 production DB. We are looking for a solution that would provide us information to identify an IP address, system name or the likes to stop this process. We have set a trigger that captured the user name and when the process started but that was all we got back. We tried using these commands APP_NAME(), HOST_NAME(), SUSER_SNAME(), GETDATE(), but only the  SUSER_SNAME() and GETDATE() returned data.

Any help or suggestions would be greatly appreciated.

Thanks experts.
Avatar of lcohan
lcohan
Flag of Canada image

I would set a SQL Profiler trace and look for(filter) by "user name" you got from your trigger and make sure to include the Host in the trace definition. You may not get the IP but the name you will get there must resove back to an IP no matter waht - just make sure you don't run the trace on that same server and not the Default trace against a busy SQL server as it can bring it down. Start from the default but select columns you need (I always drop bynary) and make sure you set the filter like that user name - %user_name%
Bit more details here: http://dbtricks.com/?p=87
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bjbrown

ASKER

Thanks lcohan, I will set this to run tonight when the process we are trying to track runs and see what we get. Thanks for the info.
I just came accross scripts below if you know the SPID of that SQL process from the trace - I knew I have something because I had similar issue trying to get the host IP or name in my DDL database triggres that capture who/when changed my db objects:



SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;



SELECT  CONNECTIONPROPERTY('net_transport') AS net_transport,
         CONNECTIONPROPERTY('protocol_type') AS protocol_type,
         CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
         CONNECTIONPROPERTY('local_net_address') AS local_net_address,
         CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
         CONNECTIONPROPERTY('client_net_address') AS client_net_address
Avatar of bjbrown

ASKER

This looks interesting, I'll give that a try and let you know. It's a real mystery here and unfortunately is holding up the installation of the product that will be populating these tables. Obviously we have to stop whatever process is already doing this before proceeding.