# Sql-server – Correlating hostname/IP to user

Securitysql serversql-server-2008sql-server-2012

I work in an environment where logins are shared among applications, and occasionally developers – in a situation like this, I am having trouble determining who is connected to the database, or who is running particular queries.

The most specific information I can find seems to be host_names, or an IP address.

I am wondering, if there is a way to correlate this information with a user?

If you know which workstation belongs to which human, you can create a logon trigger that logs all instances of the specific shared user(s) over a period of time, and you can review the table to see which workstations are logging in. I would put the logging table in a stable place like msdb or a utility database if you have one.

USE msdb;
GO
(
EventDate datetime2 NOT NULL DEFAULT sysdatetime(),
HostName sysname,
);


Now, the logon trigger:

USE master;
GO
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

SELECT HostName, IPAddress, COUNT(*), MAX(EventDate)