Get Login Info

A tiny utility procedure for auditing and support calls: it looks up the current session in sysprocesses and returns the connecting host, the Windows domain, and the user name - preferring the NT username when one exists, falling back to the SQL loginame otherwise. Handy for figuring out who is actually connected when a report comes in.

CREATE DROP PROCEDURE GetLoginInfo AS
BEGIN
	SELECT TOP 1
		RTrim(LTrim(SysP.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS AS [Host],
		RTrim(LTrim(SysP.nt_domain)) COLLATE SQL_Latin1_General_CP1_CI_AS AS [Domain],
		CASE WHEN RTrim(LTrim(SysP.nt_username)) <> '' THEN
			RTrim(LTrim(SysP.nt_username))
		ELSE
			RTrim(LTrim(SysP.loginame))
		END COLLATE SQL_Latin1_General_CP1_CI_AS AS [User]
	FROM
		master..sysprocesses AS SysP
	WHERE
		SysP.spid = @@SPID
END
GO