473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

User ip tracking

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!

Joshua

Jun 20 '07 #1
2 4644
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
Jun 20 '07 #2
On Jun 20, 3:50 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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!

Jun 21 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Don | last post by:
I have a need to submit a form, but don't need the user to click on a button. How do I do this? Is there some way, using JavaScript, to setup a <form> tag to do this? Thanks, Don ----==...
5
by: byrocat | last post by:
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...
3
by: Kyle Friesen via AccessMonster.com | last post by:
Mike, I have databse that creates a "tracking number" based on the selections made on the form via concatenating. At the end of the tracking number, I need a two digit (01-99) sequence number by...
6
by: BBFrost | last post by:
I'm using Net 1.1 (2003) SP1 & Windows 2000 Here's the issue ... Rows 12 thru 24 are selected in a datagrid. The user now unselects rows 12 thru 24 and selects rows 45 thru 70 ??? How can...
1
by: kasterborus | last post by:
Hi, My client is using the UBB Threads forum and I was recently asked to look into adding the ability to track the activity of each of the users to see who is more active than others. Users are...
26
by: beporter | last post by:
First time poster here! Let me get straight to it... I'm currently not in a position that lets me interact with other developers face to face on a regular basis, and I am in need of some "round...
1
by: bdockery | last post by:
So I figured out that if you use this html: http://wwwapps.ups.com/WebTracking/processInputRequest?sort_by=status&tracknums_displayed=1&TypeOfInquiryNumber=T&loc=en_US&InquiryNumber1=<TRACKING...
3
by: =?Utf-8?B?R3JhaGFt?= | last post by:
I've added 2 tracking services to the wf runtime; one is the standard SqlTrackingService: trackingService = new SqlTrackingService(<trackingConnectionString>); <workflow...
9
by: sitko | last post by:
Hi, I have an Order tracking spreadsheet that I need help with. I have a 2 worksheets "Open", and "Closed". I have entries on the "Open" sheet which may or may not be grouped together. I've...
10
junior29
by: junior29 | last post by:
Hi, I just wondered if anyone could help me at all. I'm doing a piece of coursework building a social network (which is getting there!), we are supposed to add an administration element to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.