Tracking user activities

Sybase and DB2 both have the capability of tracking user activities at
a number of levels: invalid access attempts to databases, table, etc.;
creation/deletion/modification of database objects/users/groups,
grants/revokes.

For MS SQLServer, the only setting that I've seen in the documentation
is access attempts (none, fail only, etc.)

The monitor program has the capability of tracking the events that I
want to be monitored, but it seems as though these settings persist
only while the monitor program is running.

I'd like these settings to persist permanently and the event records to
be sent to the system log.

I can't seem to find the right term to get this information out of the
MS Books On LIne.

Help!

byrocat (bdealhoy@sympatico.ca)  writes:
> Sybase and DB2 both have the capability of tracking user activities at
> a number of levels: invalid access attempts to databases, table, etc.;
> creation/deletion/modification of database objects/users/groups,
> grants/revokes.
>
> For MS SQLServer, the only setting that I've seen in the documentation
> is access attempts (none, fail only, etc.)
>
> The monitor program has the capability of tracking the events that I
> want to be monitored, but it seems as though these settings persist
> only while the monitor program is running.
>
> I'd like these settings to persist permanently and the event records to
> be sent to the system log.
>
> I can't seem to find the right term to get this information out of the
> MS Books On LIne.

In SQL 2000, this is not very simple to do. In SQL 2005, which is just
around the corner you can create triggers on database and server to
capture this kind of stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
 news:Xns96EFAAA29FDC
FYazorman@127.0.0.1...
> byrocat (bdealhoy@sympatico.ca) writes: 
>
> In SQL 2000, this is not very simple to do. In SQL 2005, which is just
> around the corner you can create triggers on database and server to
> capture this kind of stuff.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

I don't know how simple it is, but if the event is captured by SQL Profiler
you can use Profiler to define a trace and then under the file menu item
select Script Trace.  This will generate a script that uses sp_trace_create,
sp_trace_setevent, sp_trace_setfilter, and sp_trace_setstatus to create a
server side trace.  You can then use fn_trace_gettable to retreive data from
the trace file as a table.

Mike Reigler

MIke, thanks for the reply.  Two more quick questions:

So, if we go through the exercise of defining the events that we wish
to trace and generate the script file and then run the script file,
this means that the settings are permanent (i.e., rebooting the server
will not terminate the settings)?

Can I also assume that the script can be used to set up can be used on
other servers without changes?

Thanks in advance!

byrocat (bdealhoy@sympatico.ca)  writes:
> So, if we go through the exercise of defining the events that we wish
> to trace and generate the script file and then run the script file,
> this means that the settings are permanent (i.e., rebooting the server
> will not terminate the settings)?

First of all, since you probably want to look at the data, and you
can do that while the trace is running, you would need a job that
stops the trace, moved the file away and then restarts the trace.

You can specify a max size, and a rollover, so that the engine performs
this trick when the file has reached a certain size. But if it takes
a while for the file to fill up, that's not good.

And, no, the trace does not survive a server reboot. Put the script
in a stored procedure, and mark this procedure as a startup procedure.

> Can I also assume that the script can be used to set up can be used on
> other servers without changes?

Yes.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp