Friday, June 29, 2012 11:29 AMCan we audit on sql logins from SSMS and show which windows user or machine connected?
Friday, June 29, 2012 12:49 PM
You can use sys.dm_exec_connections DMV to get details of current connections
Sunday, July 01, 2012 6:11 AM
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)
Sunday, July 01, 2012 7:24 AM
I use Adam's great utility
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/
Tuesday, July 03, 2012 1:13 PM
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.)
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:
All the best,