SQL Login Audit

Question

Can we audit on sql logins from SSMS and show which windows user or machine connected?

Answer 1

You can use sys.dm_exec_connections DMV to get details of current connections

http://msdn.microsoft.com/en-us/library/ms181509.aspx

-Rohit 

Answer 2

Hi Nibras33,

You can get the current login connection with what operation they are doing by following

SELECT * FROM SYS.SYSPROCESSES CROSS APPLY ::FN_GET_SQL(@SQL_HANDLE)

http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/cc280386.aspx

Answer 3

I use Adam's great utility

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 4

The previous answers focus on examining the existing connections.  It seems that you are only concerned about who is using SSMS to connect to your server.  If that is your narrow focus, perhaps you want to record the SSMS logins in an audit table.

You could create a login trigger that would record data only when it seems to be a connection from SSMS.  The login event data includes the login_name and the client_host, but not the program name.  As mentioned earlier, the sys.dm_exec_sessions DMV also contains the Program Name.   (Notice that client_host and program name are not 100% reliable, since the application sets those values.)

http://msdn.microsoft.com/en-us/library/bb326598.aspx

Here is a 2006 article on using logon triggers: http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/

Also, you can use event notifications for auditing purposes.  These are asynchronous and would record all logins. See the following to get started:

http://msdn.microsoft.com/en-us/library/ms186376.aspx
http://msdn.microsoft.com/en-us/library/ms189453.aspx

All the best,
RLF