如何获取SQL Server数据库中近30天有连接记录的活跃用户列表

你之前使用的sysusers是存储数据库全部用户基础元数据的系统视图，本身不记录用户的连接、操作行为，因此无法区分用户是否活跃，可参考以下方案查询符合要求的活跃用户：

前置说明 #

• SQL Server默认不会永久存储全量历史连接记录，以下方案的可用性依赖实例的日志保留配置
• 动态管理视图sys.dm_exec_sessions仅保留实例上次重启之后的会话数据，实例重启后数据会清空
• 若需要长期查询历史活跃用户，建议提前开启服务器级登录审计或扩展事件跟踪

方案1：查询实例重启后近30天的活跃用户 #

如果你的实例近30天内没有重启，可以直接执行以下语句，查询近30天有过连接记录的用户：

SELECT DISTINCT 
    login_name AS 登录名,
    nt_user_name AS 域用户名,
    MIN(login_time) AS 首次登录时间,
    MAX(login_time) AS 最近登录时间
FROM sys.dm_exec_sessions
WHERE login_time >= DATEADD(DAY, -30, GETDATE())
GROUP BY login_name, nt_user_name
ORDER BY 最近登录时间 DESC

方案2：通过错误日志查询历史登录记录 #

如果实例近30天内有过重启，且你没有清理过对应时段的错误日志，可以通过读取错误日志中的登录成功记录统计活跃用户：

-- 创建临时表存储日志内容
CREATE TABLE #ErrorLog (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    LogText VARCHAR(MAX)
)

-- 读取错误日志中的登录成功记录，参数0代表读取当前日志，需要读更早的日志可将0改为1、2等序号
INSERT INTO #ErrorLog
EXEC sp_readerrorlog 0, 1, 'Login succeeded'

-- 筛选近30天的活跃用户
SELECT DISTINCT 
    SUBSTRING(LogText, CHARINDEX('''', LogText) + 1, CHARINDEX('''', LogText, CHARINDEX('''', LogText) + 1) - CHARINDEX('''', LogText) -1) AS 登录名,
    MAX(LogDate) AS 最近登录时间
FROM #ErrorLog
WHERE LogDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY SUBSTRING(LogText, CHARINDEX('''', LogText) + 1, CHARINDEX('''', LogText, CHARINDEX('''', LogText) + 1) - CHARINDEX('''', LogText) -1)
ORDER BY 最近登录时间 DESC

DROP TABLE #ErrorLog

如果以上两种方案都无法获取到所需记录，说明实例没有保留对应时段的历史日志，暂时无法追溯历史活跃用户。

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