Great article Brian. I have a question. The following query you presented only works in SQL2005 server. I have several SQL2000 server installations in my shop. How can I check the authentication method in SQL2000 server then? Thanks.
SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.auth_scheme
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id