SQL Server登录查询需求：筛选6个月未使用的登录账户

一、查询所有登录账户及最后登录详情 #

SQL Server中SQL登录的最后登录时间可直接从系统视图获取，Windows登录的最后登录时间则依赖服务器审计（需提前开启登录审核）。以下查询整合所有登录账户的基础信息、最后登录时间及AD账户状态：

-- 整合SQL/Windows登录的完整信息
SELECT
    sp.name AS login_name,
    sp.type_desc AS login_type,
    -- SQL登录的最后登录时间
    sl.last_login_time,
    -- Windows登录的最后登录时间（从审计日志提取，替换为你的审计文件路径）
    (SELECT MAX(event_time) 
     FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
     WHERE session_server_principal_name = sp.name
       AND action_id = 'LGI') AS windows_last_login_time,
    -- 合并统一的最后登录时间
    COALESCE(sl.last_login_time, 
             (SELECT MAX(event_time) 
              FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
              WHERE session_server_principal_name = sp.name
                AND action_id = 'LGI')) AS last_login_time,
    -- 识别AD账户是否有效（仅针对Windows登录/组）
    CASE 
        WHEN sp.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP') THEN
            CASE WHEN SUSER_SID(sp.name) IS NOT NULL THEN '正常存在' ELSE '已删除/不可用' END
        ELSE 'N/A'
    END AS ad_account_status
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
WHERE sp.type IN ('S', 'U', 'G') -- S=SQL登录, U=Windows登录, G=Windows组
  AND sp.name NOT LIKE '##%##' -- 排除系统内置特殊登录
ORDER BY sp.type_desc, sp.name;

注意：需将审计文件路径C:\SQLAudit\*.sqlaudit替换为你的实例实际审计路径。若未开启登录审计，需先在服务器属性->安全性中启用"登录审核"（选择"失败和成功登录"），或通过扩展事件捕获登录事件。

二、筛选近6个月未使用的登录账户 #

基于上述查询，筛选出近6个月未登录或无登录记录的账户，同时标记无效AD账户：

-- 筛选近6个月未活跃的登录账户
WITH AllLogins AS (
    SELECT
        sp.name AS login_name,
        sp.type_desc AS login_type,
        COALESCE(sl.last_login_time, 
                 (SELECT MAX(event_time) 
                  FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
                  WHERE session_server_principal_name = sp.name
                    AND action_id = 'LGI')) AS last_login_time,
        CASE 
            WHEN sp.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP') THEN
                CASE WHEN SUSER_SID(sp.name) IS NOT NULL THEN '正常存在' ELSE '已删除/不可用' END
            ELSE 'N/A'
        END AS ad_account_status
    FROM sys.server_principals sp
    LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
    WHERE sp.type IN ('S', 'U', 'G')
      AND sp.name NOT LIKE '##%##'
)
SELECT
    login_name,
    login_type,
    last_login_time,
    ad_account_status,
    CASE 
        WHEN last_login_time IS NULL THEN '从未登录'
        WHEN last_login_time < DATEADD(MONTH, -6, GETDATE()) THEN '近6个月未登录'
        ELSE '正常活跃'
    END AS login_status
FROM AllLogins
WHERE 
    last_login_time < DATEADD(MONTH, -6, GETDATE())
    OR last_login_time IS NULL
ORDER BY login_type, login_name;

关键说明：

• last_login_time IS NULL的账户分为两种情况：从未登录过，或审计/扩展事件未捕获到登录记录，需结合实际运维情况确认。
• 通过SUSER_SID(sp.name) IS NOT NULL判断AD账户有效性：若AD账户已删除，该函数会返回NULL，可快速定位无效的Windows登录记录。

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