Re: Tracking user activities


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns96EFAAA29FDCFYazorman@xxxxxxxxxxxx
> byrocat (bdealhoy@xxxxxxxxxxxx) 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@xxxxxxxxxxxxx
>
> 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


.

Relevant Pages

  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • RE: Upgrade to Windows 2008
    ... database files, including the system databases (like master, model, msdb, ... new SQL Server, using the tools, Client Network utility and Server Network ... 2005 from scratch too, which means all the server settings, logins, user ...
    (microsoft.public.sqlserver.server)
  • Re: How to look at parameter string
    ... I am tracing, but the problem I can't trace (at least, I haven't figured out ... string it is putting together to send to Sql. ... >> The problem is not coming from Sql Server but from my page. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: C2 SQL auditing
    ... Andrew J. Kelly SQL MVP ... Do you suggest any third party tools for SOX compliance? ... I would like to off load the server side trace to the SOX team ... when I stop SQL server then my trace file gets populated. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL logins logged where
    ... you can't trace directly to a table but can use the> fn_trace_gettable function to load the trace file into a> table. ... If you are using SQL Server 7, use> xp_trace_setqueuedestination to set the destination to a> table. ... Security Tab does not give too many ...
    (microsoft.public.sqlserver.security)