使用DDL触发器捕获login和user的创建和修改

背景：

今天同事跟我反应，自己用的一个账号没办法登陆了，查询后我发现user和login都存在，但是两者之间已经没有了联系，产生了孤立账号，其实之前遇到过这种情况，在DB迁移的时候，但是我发现本次应该不是这个问题，因为我查询了最近DB的还原记录，最近都没有还原DB的操作，所以DB备份还原的操作应该排除，我写了一个DDL触发器去监控，希望后面会有收获，下面是对应的操作。

 

代码：

Create TRIGGER [Tri_Catch_Modify_LoginUser]
ON ALL SERVER 
after DROP_login ,CREATE_LOGIN,ALTER_LOGIN,CREATE_USER,ALTER_USER,DROP_USER
AS

    IF SERVERPROPERTY ('IsHadrEnabled') = 1
    BEGIN
    DECLARE @RoleDesc NVARCHAR(60)=''

    SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS rp ON rp.replica_id = a.replica_id AND rp.replica_server_name = SERVERPROPERTY ('ServerName') 
    JOIN sys.availability_groups AS ag ON ag.group_id = rp.group_id
    JOIN sys.availability_databases_cluster AS clusters ON clusters.group_id = ag.group_id AND clusters.database_name = 'CustomDB'

    IF @RoleDesc <> 'PRIMARY'
    BEGIN
        insert [cmStagingConnectODSLink].CustomDB.dbo.Catch_Modify_LoginUser
            SELECT HOST_NAME() , SYSTEM_USER AS 'Login Name', Cast(ConnectionProperty('client_net_address') As Varchar(20)),getdate(),CAST(EVENTDATA() AS VARCHAR(MAX))
    END    
    else
    begin
       insert CustomDB.dbo.Catch_Modify_LoginUser
           SELECT HOST_NAME() , SYSTEM_USER AS 'Login Name', Cast(ConnectionProperty('client_net_address') As Varchar(20)),getdate(),CAST(EVENTDATA() AS VARCHAR(MAX))
    end
    END

相关:

--查询所有的触发器事件
select * from sys.trigger_event_types where type_name like '%login%'
select * from sys.trigger_event_types where type_name like '%user%'