Posted by Erland Sommarskog on 06/20/07 21:50

jlaustill@gmail.com (jlaustill@gmail.com) writes:
> Hello everyone, I have a fairly unique need :) I am trying to
> determine the use/clients for databases in my corporation that I am
> maintaining, but that noone seems to know what they are for. Many of
> these databases never seem to have anybody connected to them in the
> current activity.
>
> What I'd like to do is find a way to audit the logins, so everytime
> someone connects to a database it simply logs the clients IP address,
> what login they used, and maybe what time. I've been searching google
> for this and have found tons of information on auditing the logins,
> but not the clients, such as by ip. Any help in this regard would be
> GREATLY appriciated!

First: next time you ask a question like this, please state which version
of SQL Server you are using. Given the nature of the question, I will assume
SQL 2000.

Seems like it's time to run a trace. The trace would filter for the database
id, and I think it's best to have one trace per database. You would specify
a fairly low max size for the traces, without any rollover option. This is
because if you happen to trace a database that has lot of activitity, you
want to load the server with the trace. And the nice thing is that after a
while you can check which traces that are still running.

Which events would you include in the trace? The first that comes to mind
is SQL:BatchCompleted, but a datbase may be referenced from another database
in a query. I would take one of Object:Opened or Locks:Acquired. Both
of these are likely to generate tons of events as soon as a database
gets used.

I don't know how much experience you have server-side traces, or Profiler
for that matter. But you need to run these traces server-side, to reduce
the load. You can still use Profiler to set up a template trace, and
then generate a script from the trace.

I suggest that you try this out on a test server, before you go live.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]