is there a log that will show client connections like a listener log file

Question

I'm wondering if there is a log file that can be searched which would list client connections similar to the listener log file used by another database product?

I'd like to know who (client name, ip address, etc.) has been connecting to my database if possible.

Answer 1

Hello,

You can get actual login information from system view sys.sysprocesses and sys.dm_exec_connections.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hello Olaf,

Do you think that it would be possible to use a logon trigger to track this type of informations ?

http://msdn.microsoft.com/en-us/library/bb326598(SQL.100).aspx

I never use a logon trigger as i had once a problem with it : the login became impossible ( an error of beginner... ), but it is not a reason to reject its use.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi Gravy King,

We can also use SQL Profiler to monitoring client connections. However, please do not run it on a very busy server for a long time because of additional resource usage. 

Reference: SQL Server Event Class Reference

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Do you think that it would be possible to use a logon trigger to track this type of informations ?
.... I never use a logon trigger as i had once a problem with it : the login became impossible ( an error of beginner... ), but it is not a reason to reject its use.

Hello Papy,

I created a logon trigger to log the users and in the first test I trapped into the same issue; an error in the logon trigger and no one could logon, even not me as SysAdmin.

So prepare it on a test system: Stopp SQL Server service, copy the "master" database files, start the service and do your test. If it fails, stopp service and replace master files and then next try.

One problem is, the table for the log has to be located in the master database itself. So I created a SP to write the log to table and granted execution right for the SP to database role "Public" to ensure everyone can execute the SP. Of course the SP is called from the logon trigger and also of course within a big TRY/CATCH block to ensure it works under all circumstances.

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing