I want to construct an audit process that leverages on the existing replication mechanism. Since I can modify the stored procedures called in the subscriber database I know that this is possible. However I also want to log the user that made the changes. I cannot find any information as to whether this is available from the transaction log. The only way that I can think of keeping track of who made what change is to have a field in every table I want to audit that gets updated using a trigger. Is there any documentation regarding the transaction log and what is accessible?
With SQL 6.5 you had access to SYSLOGS, which you could read, see posting Reading Transaction Logs.
After that SQL Server now stores logs in .LDF files. To track data modification you could using triggers as you suggested or turn on SQL Profiler to audit data access and have the output written to disk. At our site we have about 2000 users and we have a nightly job that starts up SQL Profiler using xp_trace procedures to define the trace and set the output to be a new file each day YYYYMMDD_HHMMSS.trc. There seems to be little added stress if any, we average about 2 txns/sec, with an avg. response time of 0.02 sec. However everything we do is through stored procedures.
Either way each user must have there own userid if you want to know who exactly did the modification, which isn't straight forward when the application is through IIS.
Thanks for the info. Yes, I did find what you mentioned about the transaction log. We are using SQL Server 2000. I think for now I am just going to create shadow tables and use triggers for update and delete to see what data was changed and by whom. What I need is not only who did the change and when but also the actual data that was changed. That is why the replication would have been perfect if somehow I could also see who made the change from the transaction log. My idea of having a field in the table that enters the user that made the change will not work for a delete. The user info that comes accross to the replication agent is what is in the row that was deleted not who actually did it.