社区首页 >专栏 >SQLServer记录密码变更的几种方式

SQLServer记录密码变更的几种方式

原创

保持热爱奔赴山海

发布于 2025-06-25 01:01:50

1760

方法1 使用自定义日志表 + DDL 触发器

虽然 SQL Server 不会在审计日志中直接记录密码变更的具体内容（如新密码），但你可以通过 DDL 触发器在每次执行 ALTER LOGIN 操作时捕获相关信息，并将操作记录到一个自定义的日志表中。这种方式虽然无法记录密码本身，但可以记录谁在什么时候对哪个账号执行了密码修改操作。

创建日志表

use master;
CREATE TABLE dbo.PasswordChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventTime DATETIME DEFAULT GETDATE(),          -- 事件发生时间
    LoginName NVARCHAR(128),                        -- 被修改的登录账户名
    ChangedBy NVARCHAR(128),                        -- 执行操作的登录账户名
    ChangeType NVARCHAR(50),                        -- 变更类型（例如：密码修改）
    AdditionalInfo NVARCHAR(MAX)                    -- 附加信息（可选）
);

创建一个DDL触发器

CREATE TRIGGER trg_PasswordChangeAudit
ON ALL SERVER
FOR ALTER_LOGIN
AS
BEGIN
    DECLARE @LoginName NVARCHAR(128);
    DECLARE @ChangedBy NVARCHAR(128);
    DECLARE @EventData XML;
    -- 获取当前执行操作的登录账户名
    SET @ChangedBy = ORIGINAL_LOGIN();
    -- 获取事件数据（包括被修改的登录账户名）
    SET @EventData = EVENTDATA();
    -- 从事件数据中提取被修改的登录账户名
    SET @LoginName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
    -- 判断是否是密码修改操作（注意：无法直接判断是否修改了密码，但可以假设 ALTER LOGIN 可能涉及密码修改）
    -- 这里我们简单地将所有 ALTER LOGIN 操作都记录为可能的密码修改
    INSERT INTO dbo.PasswordChangeLog (LoginName, ChangedBy, ChangeType)
    VALUES (@LoginName, @ChangedBy, '密码修改');
END;

执行几个修改密码的操作

ALTER LOGIN [devops] WITH PASSWORD = 'Abcd123!', CHECK_POLICY = ON;
ALTER LOGIN [sa] WITH PASSWORD = 'Abcd123!', CHECK_POLICY = ON;
ALTER LOGIN [sa] WITH PASSWORD = 'Abcd09876', CHECK_POLICY = ON;

查询日志表

SELECT * FROM dbo.PasswordChangeLog;

方法2 历史密码hash比对

创建历史表存储密码哈希

use master;
CREATE TABLE dbo.LoginPasswordHistory (
    LogID INT IDENTITY PRIMARY KEY,
    LoginName NVARCHAR(128),
    PasswordHash VARBINARY(256),
    ChangeTime DATETIME DEFAULT GETDATE()
);

定期记录密码哈希（需手动或通过作业执行）

INSERT INTO dbo.LoginPasswordHistory (LoginName, PasswordHash)
SELECT name, password_hash FROM sys.sql_logins;

查询历史表中sa的密码哈希记录

select * from dbo.LoginPasswordHistory where LoginName='sa';

方法3 使用服务器审计功能

创建服务器审计对象

CREATE SERVER AUDIT PasswordChangeAudit TO FILE (FILEPATH = 'D:\mssql2022\auditlogs\');

启用审计

ALTER SERVER AUDIT PasswordChangeAudit WITH (STATE = ON);

创建审计规范，捕获 ALTER LOGIN 操作

CREATE SERVER AUDIT SPECIFICATION AuditSpec_PasswordChange
FOR SERVER AUDIT PasswordChangeAudit
ADD (SUCCESSFUL_LOGIN_GROUP),          -- 可选：成功登录
ADD (FAILED_LOGIN_GROUP),              -- 可选：失败登录
ADD (DATABASE_OBJECT_CHANGE_GROUP),    -- 数据库对象变更（不包含密码变更）
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), -- 权限变更
ADD (SERVER_PRINCIPAL_CHANGE_GROUP);   -- 服务器主体（登录账户）变更，**包括 ALTER LOGIN**

启用审计规范

ALTER SERVER AUDIT SPECIFICATION AuditSpec_PasswordChange WITH (STATE = ON);

执行一些模拟修改密码的命令

ALTER LOGIN [devops_test] WITH PASSWORD = 'NewPassword123!', CHECK_POLICY = ON;

查询日志

SELECT  * 
FROM sys.fn_get_audit_file ('D:\mssql2022\auditlogs\PasswordChangeAudit*.sqlaudit', NULL, NULL)
where 1=1 and statement like '%alter%'
order by event_time desc ;

或者

SELECT 
	event_time,
	host_name,
	database_name,
	object_name, 
	statement ,
	action_id,
	succeeded,
	target_server_principal_name
FROM sys.fn_get_audit_file ('D:\mssql2022\auditlogs\PasswordChangeAudit*.sqlaudit', NULL, NULL)
where 1=1
-- and statement like '%alter%'
order by event_time desc ;

注意：

如果是通过T-SQL改密码的话，statement中会记录类似到 ALTER LOGIN [sa] WITH PASSWORD = '******', CHECK_POLICY = ON; 这种明确体现密码修改的记录。

如果是通过SSMS的图形界面修改的话，只能记录到类似：ALTER LOGIN [lirulei23] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; 这种无法明确体现出是密码出现了修改（因为在SSMS中修改某个用户的默认语言也是这种日志）

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server