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?

  • 2
    If they are using SQL authentication and/or play musical workstations then no, not really, unless you know which machine name / IP address belongs to each user. – Aaron Bertrand Jan 24 '18 at 1:35
  • @AaronBertrand each user has their own identifiable workstation, it is just the login to the database that is being shared. I would like to rectify this situation, but need to identify the users sharing logins first. I was hoping there might be a way to help me give a name to those workstations, whether that is through SQL Server or another mechanism that I might be able to gain access to. – Asher Jan 24 '18 at 2:21
  • 2
    An easy way to identify which users are using the shared login is to change the password. Those who yell are the people you're after. – Aaron Bertrand Jan 24 '18 at 2:29
  • @MichaelGreen thank you. That is the closest to what I was looking for. – Asher Jan 29 '18 at 23:22

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

Now, the logon trigger:

USE master;
  IF ORIGINAL_LOGIN() = N'that login name'
    INSERT msdb.dbo.ThatLoginLog(HostName, IPAddress)
      SELECT HOST_NAME(), client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.