jl*******@gmail.com (jl*******@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx