Reading Connection Details in Code: APP_NAME, HOST_NAME and Logins

Connection details such as APP_NAME and HOST_NAME are labels the client supplies, not proof of who connected. Only the login functions tell you who authenticated. Know which value answers your question before you put it in an audit row.

The audit row that blamed the wrong machine

Picture a small incident. Someone changed prices at an odd hour. The audit table says the change came from a host called FINANCE-PC, in the Payroll app. You walk over to that desk. The person there has never heard of it.

The audit table was not lying. It recorded exactly what the client said. The mistake was treating a label as evidence. Let me set up a small demo database so we can see which values are which.

The demo creates a server-level login called DemoLogin and a database called SqlAuthorityDemo. It removes both at the end.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
IF SUSER_ID(N'DemoLogin') IS NOT NULL DROP LOGIN DemoLogin;
GO
CREATE LOGIN DemoLogin WITH PASSWORD = 'Demo#Login-2026-x', CHECK_POLICY = OFF;
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
CREATE USER DemoUser FOR LOGIN DemoLogin;

Read the five values

Start with what your own session reports. These five functions look similar, but they answer different questions.

SELECT APP_NAME() AS application_name,
       HOST_NAME() AS host_name,
       SUSER_SNAME() AS current_login,
       ORIGINAL_LOGIN() AS original_login,
       SESSION_USER AS database_user;

The first two, application_name and host_name, come from the client. In sqlcmd the application shows as SQLCMD. In SSMS you get a longer name. Anyone can set an application name in a connection string.

The next two come from authentication. original_login is who connected. current_login is who you are right now, which can change. The last, database_user, is the user inside this database.

Watch impersonation change the context

Now impersonate. EXECUTE AS LOGIN switches the current login to DemoLogin, but original_login stays the same. EXECUTE AS USER switches the database user to a user created without a login. Again, original_login does not move.

EXECUTE AS LOGIN = 'DemoLogin';
SELECT SUSER_SNAME() AS current_login, ORIGINAL_LOGIN() AS original_login,
       SESSION_USER AS database_user;
REVERT;

CREATE USER ContextProbe WITHOUT LOGIN;
EXECUTE AS USER = 'ContextProbe';
SELECT SUSER_SNAME() AS current_login, ORIGINAL_LOGIN() AS original_login,
       SESSION_USER AS database_user;
REVERT;
DROP USER ContextProbe;

In the first result, current_login is DemoLogin and database_user is DemoUser. In the second, database_user is ContextProbe and current_login shows a long SID-style value, since that user has no login. In both, original_login is unchanged. That column is the one to keep for audit.

Treat client names as labels

Now an audit table with defaults. Each default captures one detail when a row is inserted. I add a column called RequestUser, filled from SESSION_CONTEXT, so the business user travels as its own field.

The second insert supplies its own labels. The table accepts them, because a default is only a suggestion. That is exactly how FINANCE-PC could end up in an audit row.

DROP TABLE IF EXISTS #ConnectionAudit;
CREATE TABLE #ConnectionAudit
(AuditId int IDENTITY PRIMARY KEY,
 AppLabel nvarchar(128) NULL DEFAULT APP_NAME(),
 HostLabel nvarchar(128) NULL DEFAULT HOST_NAME(),
 OriginalLoginName sysname NOT NULL DEFAULT ORIGINAL_LOGIN(),
 RequestUser nvarchar(128) NULL DEFAULT CONVERT(nvarchar(128), SESSION_CONTEXT(N'RequestUser')));

EXEC sys.sp_set_session_context @key = N'RequestUser', @value = N'user-42';
INSERT #ConnectionAudit DEFAULT VALUES;
INSERT #ConnectionAudit (AppLabel, HostLabel) VALUES (N'Payroll', N'FINANCE-PC');

EXEC sys.sp_set_session_context @key = N'RequestUser', @value = NULL;
INSERT #ConnectionAudit DEFAULT VALUES;

SELECT AuditId, AppLabel, HostLabel, OriginalLoginName, RequestUser
FROM #ConnectionAudit ORDER BY AuditId;

Row 1 holds the real labels and user-42. Row 2 holds the invented Payroll and FINANCE-PC, yet OriginalLoginName is the same in all three rows. Row 3 shows what a reset does: RequestUser is empty again.

Pooled connections need a reset

That reset matters. A pooled connection outlives one request. If the app sets RequestUser and forgets to clear it, the next request inherits the previous user. Set it from trusted code at the start of each request, and clear it at the end.

One more query is worth keeping for troubleshooting. It counts sessions by program name, so a surprise application stands out.

SELECT program_name, COUNT(*) AS sessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY program_name
ORDER BY program_name;

You see only what your permissions allow. Collect what you need and no more, and limit who can read the audit data. Last, clean up.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP LOGIN DemoLogin;

Keep the labels, the login and the business user in separate columns, and trust only the login.

A client label is not authenticated identity, it is troubleshooting context.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.