Posted by jlaustill@gmail.com on 06/21/07 13:08

On Jun 20, 3:50 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> jlaust...@gmail.com (jlaust...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland,

Thanks for your response, I hadn't even thought about using
profiler :) I've used it extensively as a Database Developer to run
traces for optimizations, but never took the time to realize that it
could be used as a security audit tool. Setting this up took only
minutes, and within minutes I had my answers and knew who was using
what databases. I now have contacts for each of them.

I assume WAY to much when I write posts, but you were correct in
assuming 2000. I'm the king of asking questions without giving enough
information when I'm frustrated, thanks again man!

[Back to original message]