up vote 2 down vote favorite
share [g+] share [fb]

Is there a way to find the list of workstation that have connected to my SQL Server (2000) or to one of it's databases over its life time? Even a couple weeks would do for my purposes.

Is there a log file somewhere, or is it something I have to turn on?

link|improve this question
78% accept rate
feedback

1 Answer

up vote 2 down vote

You first need to enable connection auditing, which is not on by default. In Enterprise Manager, right-click on the server instance and choose properties. Then set the Audit Level option on the Security tab. When enabled SQL Server will log connection information into the SQL Server Log. You can also see connection information through SQL Server Profiler traces when auditing is enabled.

Keep in mind that the server instance has to be reachable for connection events to be raised, obviously.

If your server is not auditing connection attempts then you will have to revert to piecing together information from the Windows event logs.

Have a read through SQL Server 2000 Auditing on Technet.

link|improve this answer
And I see you have to restart the service as well. – CodeSlave Feb 1 '11 at 20:22
After further experimentation, it seems that this only gives me the Usernames of those who has connected, not the actual workstations. – CodeSlave Feb 2 '11 at 16:00
@CodeSlave - For that you'll need a profiler trace (sorry for the confusion!) – squillman Feb 2 '11 at 17:38
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.