Tracking user activities

[byrocat]

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!

[Erland Sommarskog]

byrocat (bd******@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, es****@sommarskog.se

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

[Mike Reigler]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

byrocat (bd******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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

[byrocat]

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!

[Mike Reigler]

"byrocat" <bd******@sympatico.ca> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

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!

The server side trace is not really permanent it just runs from when you
start it until it is stopped. I think rebooting the server would stop the
trace. You would have to use job scheduling to run the script on startup.

Also when you create the trace you specify how large the trace file can be
(the default is 5 MB). If you want to permanently capture the data you
would need to have a script scheduled to run periodically before the file
gets to the max size and copy the data from the trace file into a permanent
table and probably start a new trace.

Not exactly simple, but as long as your trace doesn't filter on specific
users etc. it should be portable to other servers.

All that being said, I've never run a trace permanently just used them to
troubleshoot a specific problem.

[Erland Sommarskog]

byrocat (bd******@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, es****@sommarskog.se

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