0

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.

2
  • 2
    May be it's because a logon trigger fires only upon logon? Besides, queries (select statements) never trigger anything. Looks like what you want is audit.
    – mustaccio
    Nov 18 '21 at 14:42
  • I understand, thank you very much Nov 18 '21 at 15:53
2

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:

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.