Detect remote sql user

 Issue : - I have a couple of remaining very legacy database servers who flash their sql ports to the outside world. They contain about 100 databases between them and are all legacy customers so it's a tough sell to contact these customers.

Many, e.g. 10-15 or so have datafeeds or otherwise use this direct database access. I want to identify who these users are so I can contact them, get their ip details, then lock SQL access to the servers correctly.

How can I capture the unique external IPs and their database destinations in MS SQL 2000 to this end, e.g. over a period of a month?

My Solution : - To set up a SQL Profiler trace that tracks users who have logged in or out of the server, do the following:

   1. Create a trace, selecting Audit Login Event.

   2. To return the appropriate information, specify the following data columns:

      EventClass (selected by default)

      EventSubClass

      LoginSID

      LoginName

    * Monitor Transact-SQL activity per user.

      You can create a trace that captures events relating to the Sessions event class, ExistingConnection, and TSQL event classes. Include all data columns in the trace, do not specify any event criteria, and group the captured events by DBUserName.


****AS IS**** ; Please apply at your own risk.