SQL Server 'SA' account successful logon Alert

Sreenivasa, in SQL Server you have Logon Triggers that fires every time a logon occurs. If you want to send an email only a 'sa' Login fires the trigger then you can do something like:

CREATE TRIGGER trg_login
ON ALL SERVER 
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' 
    EXEC msdb.dbo.sp_send_dbmail @recipients = 'SecurityTeam@company.com', @body = 'ALERT: sa login detected.', @subject = 'SA Login' ;
END;

Select allOpen in new window

Hi Vitor,
It is working fine. I have modified the below email send line to inserting data into a temp table for testing purpose. It is working fine. But for single 'sa' login (using SQL Server Authentication mode) it inserting three records in the table instead one single record. Please suggest me on this.

 --EXEC msdb.dbo.sp_send_dbmail @recipients = 'SecurityTeam@company.com', @body = 'ALERT: sa login detected.', @subject = 'SA Login' ;
    INSERT INTO [tempdb].[dbo].[TestSA]([Value])VALUES ('tsc')

Strange.
How are you logging in?

Hi vitor,
I have tried with email profile account as well, it is sending 3-5 mails when i login through SA account. I am logging via SQL Server authentication mode . User name: sa, passowrd: xxxxxxx.Please find my code and review.

CREATE TRIGGER [testSA]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa'
   
   EXEC msdb.dbo.sp_send_dbmail @profile_name='testProfile',
@recipients='xyz@abc.com', @subject='Test SA message',
@body='SA log on occured in the server '
     
END;

Sorry, I wasn't clear. What I meant is if you're logging in through an application or just SSMS?

I made some tests and looks like the logon trigger is fired many time when it SSMS. You just need to open some folders under the instance name and you get the trigger fired.

Hi Vitor,

Yes. I am trying through SSMS and not from an application. I can see trigger under server objects folder.

Thanks for the solution