vDomainUser View

A small reporting helper. This view takes SUSER_SNAME() and splits it on the backslash so you get clean Domain and Username columns - handling both DOMAIN\user and bare SQL login names. It also returns the current SPID. The commented-out section shows the older sysprocesses-based version that did the same job.

CREATE VIEW vDomainUser
AS
SELECT
	SubString(CASE WHEN CharIndex('\', SUSER_SNAME()) = 0 THEN '\' + SUSER_SNAME() ELSE SUSER_SNAME() END,
		1, CharIndex('\', CASE WHEN CharIndex('\', SUSER_SNAME()) = 0 THEN '\' + SUSER_SNAME() ELSE SUSER_SNAME() END) - 1) AS [Domain],
	SubString(CASE WHEN CharIndex('\', SUSER_SNAME()) = 0 THEN '\' + SUSER_SNAME() ELSE SUSER_SNAME() END,
		CharIndex('\', CASE WHEN CharIndex('\', SUSER_SNAME()) = 0 THEN '\' + SUSER_SNAME() ELSE SUSER_SNAME() END) + 1, 255) AS [Username],
	@@spid AS [SPID]

/*
SELECT TOP 1
	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 [Username],
	SysP.spid AS SPID
FROM
	master..sysprocesses AS SysP
WHERE
	SysP.spid = @@SPID
*/
GO