how to audit local access to sql server

hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpf
You can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegr oups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>
|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?
|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegr oups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>