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;