Correlating hostname/IP to user

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
CREATE TABLE dbo.ThatLoginLog
(
  EventDate datetime2 NOT NULL DEFAULT sysdatetime(),
  HostName sysname,
  IPAddress varchar(15)
);

Now, the logon trigger:

USE master;
GO
CREATE TRIGGER LogTheLogin
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
  IF ORIGINAL_LOGIN() = N'that login name'
  BEGIN
    INSERT msdb.dbo.ThatLoginLog(HostName, IPAddress)
      SELECT HOST_NAME(), client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;
  END
GO

Then, periodically, or as soon as you think you’ve given enough time in your business cycle for everyone to have logged in at least once:

SELECT HostName, IPAddress, COUNT(*), MAX(EventDate)
  FROM msdb.dbo.ThatLoginLog
  GROUP BY HostName, IPAddress;