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:
Code:
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
GO
Obviously, 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:
Code:
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...
Profiler Trace
Linear Mode
