Hey there, let's break down these two SQL Server user access questions with practical, actionable solutions:

The approach here depends on whether you need current active users or historical access records:

Current/Recently Active Users #

For users who are currently connected or have accessed the database recently (before their session was cleaned up), use dynamic management views (DMVs):

SELECT DISTINCT 
    s.session_id,
    s.login_name,
    db.name AS database_name,
    s.host_name,
    s.program_name,
    s.last_request_start_time
FROM sys.dm_exec_sessions s
JOIN sys.databases db ON s.database_id = db.database_id
WHERE s.is_user_process = 1; -- 排除系统进程

Note: DISTINCT removes duplicate entries from the same user session, and is_user_process = 1 filters out internal SQL Server system sessions.

Historical Access Records #

To pull past access data, you'll need to have had logging enabled beforehand. Here are two common methods:

• SQL Server Audit (if configured)
  If you set up server or database-level auditing, use the audit file reader function:

  SELECT DISTINCT 
      event_time,
      session_server_principal_name AS login_name,
      database_name
  FROM sys.fn_get_audit_file('C:\YourAuditLogPath\*.sqlaudit', DEFAULT, DEFAULT)
  WHERE action_id IN ('LGI', 'DBC') -- 锁定登录和数据库连接事件
  ORDER BY event_time DESC;
  

  Replace the file path with your actual audit log directory.

• SQL Server Error Log
  Use this to check recent successful login events (logs rotate regularly, so older records may be gone):

  EXEC xp_readerrorlog 0, 1, 'Login succeeded'; -- 0 = 当前日志, 1 = 上一份日志
  

Again, split this into real-time/recent and historical scenarios:

Real-Time/Recent Access #

Use DMVs to filter for your target database and time range:

SELECT DISTINCT 
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_start_time,
    s.last_request_end_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.database_id = DB_ID('YourTargetDatabase') -- 替换为你的数据库名
  AND s.is_user_process = 1
  AND s.last_request_start_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 23:59:59'; -- 调整时间范围

last_request_start_time记录了用户最后一次查询/操作的开始时间，能覆盖在时间窗口内访问过数据库的用户。

Historical Access (Pre-Configured Logging) #

• Audit File Method
  给审计查询添加数据库和时间筛选条件：

  SELECT DISTINCT 
      event_time,
      session_server_principal_name AS login_name,
      database_name
  FROM sys.fn_get_audit_file('C:\YourAuditLogPath\*.sqlaudit', DEFAULT, DEFAULT)
  WHERE database_name = 'YourTargetDatabase'
    AND action_id IN ('LGI', 'DBC')
    AND event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 23:59:59'
  ORDER BY event_time DESC;
  

• Extended Events (if configured)
  如果你创建了跟踪数据库访问的扩展事件会话，可以查询对应的事件文件：

  SELECT 
      event_data.value('(event/@timestamp)[1]', 'datetime') AS event_time,
      event_data.value('(event/data[@name="login_name"]/value)[1]', 'nvarchar(100)') AS login_name,
      event_data.value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(100)') AS database_name
  FROM (
      SELECT CAST(event_data AS XML) AS event_data
      FROM sys.fn_xe_file_target_read_file('C:\YourXEventsPath\SessionName_*.xel', DEFAULT, DEFAULT)
  ) AS x
  WHERE event_data.value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(100)') = 'YourTargetDatabase'
    AND event_data.value('(event/@timestamp)[1]', 'datetime') BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 23:59:59'
  ORDER BY event_time DESC;
  

  替换文件路径和会话名为你的实际配置即可。

内容的提问来源于stack exchange，提问作者moe