Profiler Trace

I know this is a bit unrelated to vb but I really need help and I know theres a few gurus out there.

Basically I have had data mysterously change with no reason, so as it had caused me over 4 hrs of work I added a trace to the database, a few nights later the same thing happened, now I found the entry that shows what happened from the trace which is here

RowNumber EventClass TextData NTUserName ClientProcessID ApplicationName LoginName SPID Duration StartTime Reads Writes CPU EventSubClass DatabaseName LoginSid
111852 41 UPDATE L320_Broadcast SET Printed = 0 2976 MS SQLEM - Data Tools sa 68 47 24/03/2005 02:15:42 6613 89 47 <Binary>

Now from this, I understand that there was a client on the network using enterprise manager which had the sa login details and was used to ru nthe update query. I assume i am correct?

So my question is can I find out anything about that client? I need to find an IP or computername or something so I can identify the PC it came from. You can see the NT user name wasnt logged I suspect this is because it was done via a vpn. Any help will be much appreciated.

If you are capturing the trace information to a table versus a file, then you could use an Insert Trigger on that table to invoke and capture the results of the sp_who stored procedure passing it the SPID from the trace event record. Contained within the results of sp_who is the field 'hostname'.

Unfortunately I was running the trace to a file due to the amount of activity it would have caused on the database server.

So unless I captured the data in realtime theres no way to trace back to an IP or hostname?

I have not found (yet) where you can include the IP or hostname in the data generated by a trace event.

A method that I employ is to include an "Audit Trail" within the database itself. Using triggers and the @@SPID global variable, I am able to then query the results of sp_who and the sysprocesses table for that SPID and populate fields (either within the same record or records in other tables). This provides real-time tracking and allows you to get as granular as you want (within what data is available of course).

As 001001b suggested, add the audit trail within the database itself.

Here's an example of one method I use:

First, add an audit table. In this example, tblAudit contains 4 fields: fld_PKID (int, identity), fld_TimeStamp (datetime), fld_User VarChar(100), and fldHost Varchar(100)

Then, add a trigger to the table that you wish to audit. Ex:
use Your_Database_Name
GO

If Exists
( Select Name from sysobjects
where name = N'Your_Trigger_Name' and Type = 'TR' )
DROP TRIGGER Your_Trigger_Name
GO

CREATE TRIGGER Your_Trigger_Name ON YourTableName

FOR INSERT, UPDATE
AS
Begin
DECLARE
@HOSTNAME VARCHAR(100),
@APPNAME VARCHAR(100)

set @HOSTNAME = host_name()
set @APPNAME = APP_NAME()

INSERT INTO tblAudit
( fld_TimeStamp,
fld_User,
fld_Host
)
Values
(
CURRENT_TIMESTAMP,
SESSION_USER + ':' + SYSTEM_USER,
@HOSTNAME + ':' + @APPNAME
)

END
GOObviously, you'll need to change Your_Trigger_Name, YourTableName, and Your_Database_Name to your actual object names.

After I updated a field and added a few fields in the table I was auditing, here's the results:select * from tblAudit

fld_id_pk fld_TimeStamp fld_User fld_Host
--------- ------------- ------------------------------------------------------------ ------------------------------------------------------------
1 3/24/2005 11: dbo:TALLEYDS\loquin LOQUIN:MS SQLEM - Data Tools
2 3/24/2005 11: dbo:master LOQUIN:Query Express
3 3/24/2005 11: dbo:master LOQUIN:Query Express
4 3/24/2005 11: dbo:master LOQUIN:Query Express

4 row(s)In the first case, I was logged on using windows authentication using Enterprise Manager to update a record, in the remaining three records, I was logged on with SQL Server authentication, using Query Express to insert new records. The fld_Host column contains the windows computer name, which may be of benefit. (interestingly, the SQL Server HOST_NAME function returns the name of the workstation, and not the name of the server.)

Since you might be accumulating a large abount of data in the database, you might want to alter the above trigger so that it ONLY adds an audit record if the user is 'sa'. Or, alter it to only add a record if it's an update, rather than an insert. (This wouldn't catch any changes that are accomnplished by deleting an existing record, then adding a new record in its place, though)

In addition, you'll probably want to enable (for the moment, anyway) under the security tab of Server properties in EM, All auditing level.

And, after you get a day's data or so, you should probably alter the sa password...

at the Trace Properties (after connecting to the server)
at the Data Columns Tab...
add HostName from the unselected data to selected data

i hope that, that's what you meant
and i hope it will work too :D