best way to audit activity

I need setup an audit trail on all CRUD that happens on a SQL 2000 database.
I don't want to enable C2 auditing since I think that would be overkill.
Creating a Profiler template and tuning it to the exact info I want seems th
e
right approach, but using the profiler is not ideal since it cannot survive
a
reboot without having to manually restart the trace. I think what I need to
do is to make a stored procedure that uses the sp_trace* stored procs to
start a trace and add at .bat file to the system startup that triggers the
stored proc to start running.
The problem is that I can't seem to find the documentation I need to l
understand how to use sp_trace_setevent. Also, it seems like it would be a
lot easier if I could just create a SQL Profiler template and have a stored
procedure just use the template. I think I've run into some mention that thi
s
is possible, but nothing more than that.
Is it possible to just use a profiler template? If so, I need code samples
to get me started, and if not, then I need complete documentation on
sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
someone point me in the right direction here?The easiest thing is to get your trace set up exactly as you want it in
profiler and then under the File->Export menu export it as a SQL Script. It
will generate a .sql file with all the commands you need to generate the
equivalent server-side trace.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>I need setup an audit trail on all CRUD that happens on a SQL 2000
>database.
> I don't want to enable C2 auditing since I think that would be overkill.
> Creating a Profiler template and tuning it to the exact info I want seems
> the
> right approach, but using the profiler is not ideal since it cannot
> survive a
> reboot without having to manually restart the trace. I think what I need
> to
> do is to make a stored procedure that uses the sp_trace* stored procs to
> start a trace and add at .bat file to the system startup that triggers the
> stored proc to start running.
> The problem is that I can't seem to find the documentation I need to l
> understand how to use sp_trace_setevent. Also, it seems like it would be a
> lot easier if I could just create a SQL Profiler template and have a
> stored
> procedure just use the template. I think I've run into some mention that
> this
> is possible, but nothing more than that.
> Is it possible to just use a profiler template? If so, I need code samples
> to get me started, and if not, then I need complete documentation on
> sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
> someone point me in the right direction here?|||1. Open Profiler.
2. Create you trace
3. Under the file menu, there is an option to save the trace which will
generate a T-SQL script with all of the commands that you need.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>I need setup an audit trail on all CRUD that happens on a SQL 2000
>database.
> I don't want to enable C2 auditing since I think that would be overkill.
> Creating a Profiler template and tuning it to the exact info I want seems
> the
> right approach, but using the profiler is not ideal since it cannot
> survive a
> reboot without having to manually restart the trace. I think what I need
> to
> do is to make a stored procedure that uses the sp_trace* stored procs to
> start a trace and add at .bat file to the system startup that triggers the
> stored proc to start running.
> The problem is that I can't seem to find the documentation I need to l
> understand how to use sp_trace_setevent. Also, it seems like it would be a
> lot easier if I could just create a SQL Profiler template and have a
> stored
> procedure just use the template. I think I've run into some mention that
> this
> is possible, but nothing more than that.
> Is it possible to just use a profiler template? If so, I need code samples
> to get me started, and if not, then I need complete documentation on
> sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
> someone point me in the right direction here?|||Oh I see. I had overlooked that option. It was greyed out until I was
actually running the trace. One thing I'd do with the script is have it
append the trace file name with the date (no problem). This would mean that
I'll have to make the trace run until I tell it to stop (let's say at
24:59:59) and make my bat file start it again at (at 1:00:00). Starting it
with a bat file seems easy enough, but how exactly would I go about stopping
the trace? Also, since I want the trace to run on all databases on the serve
r
instance, where do I put the strored proc, in the master database?
"Roger Wolter[MSFT]" wrote:

> The easiest thing is to get your trace set up exactly as you want it in
> profiler and then under the File->Export menu export it as a SQL Script.
It
> will generate a .sql file with all the commands you need to generate the
> equivalent server-side trace.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>
>|||You can specify a stop time in sp_create_trace so it stops when you want it
to. sp_trace_set_status can also be used to start or stop a trace. I would
use an agent job or a Service Broker Conversation Timer to start the trace.
Keep in mind that starting and stopping the trace runs the risk of missing
commands so just setting the file size to a reasonable size and letting
trace create new files when it needs to is probably safer. If you size it
to get about a days worth of data, the timestamp on the files should let you
find the data you want. You can use fn_trace_gettable to load the file you
want into a table to find the statements you're looking for.
Traces are global so it doesn't matter where you run it from.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:5CF44DFC-623E-4089-9034-B58A0869F811@.microsoft.com...[vbcol=seagreen]
> Oh I see. I had overlooked that option. It was greyed out until I was
> actually running the trace. One thing I'd do with the script is have it
> append the trace file name with the date (no problem). This would mean
> that
> I'll have to make the trace run until I tell it to stop (let's say at
> 24:59:59) and make my bat file start it again at (at 1:00:00). Starting it
> with a bat file seems easy enough, but how exactly would I go about
> stopping
> the trace? Also, since I want the trace to run on all databases on the
> server
> instance, where do I put the strored proc, in the master database?
>
> "Roger Wolter[MSFT]" wrote:
>