Posted by Mike Reigler on 10/14/05 20:49

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96EFAAA29FDCFYazorman@127.0.0.1...
> 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/techinfo/productdoc/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

[Back to original message]