Its possible create a trigger after every transact in all DB? (SQL SERVER)

Question

I have 100+ databases in my instace of SQL SERVER and mostly is a legacy DB but i dont have a control of what is new and what is old.

My idea is create a Trigger in all server for every transact and INSERT in table for audit.

I create this table on master:

CREATE TABLE Audit_Logins
    (Login_Name NVARCHAR(256), 
     Login_Time DATETIME,
     Db_name NVARCHAR(100),
     Host_Name  NVARCHAR(200)
    );

And try create a trigger on LOGON for store this information

CREATE TRIGGER insert_log_on_logon ON ALL SERVER
FOR LOGON
AS
        DECLARE @login NVARCHAR(200);
        DECLARE @db NVARCHAR(100);
        SET @login = ORIGINAL_LOGIN();
        SET @db = DB_NAME();
        IF(@login LIKE '%domain%' AND @db <> NULL)
     BEGIN
           INSERT INTO Audit_Logins
                SELECT ORIGINAL_LOGIN(), 
                       GETDATE(),
                       DB_NAME(),
             EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(128)');
     END

But this doesnt work, if i run a select or any other command not fire the trigger.

score 2 · Accepted Answer · answered Nov 18 '21 at 14:44

I'm not sure that SQL triggers are the best tool for this job, I'd encourage you to look into the variety of SQL's auditing-related features, or look into 3rd-party SQL auditing tools:

Its possible create a trigger after every transact in all DB? (SQL SERVER)

1 Answers1