Link to home
Create AccountLog in
Avatar of tschary
tschary

asked on

SQL Server 'SA' account successful logon Alert

Hi Experts,

I want to capture 'SA' account successful logon alone , i need to send an email alert to security team.When anyone logged in using 'SA' account. We have SQL Server audit feature to achieve this . it captures all successful logins in security log. But i need only 'SA' user account successful audit information. Please provide your tips.

Thanks in Advance,

Sreenivasa
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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;

Open in new window

Avatar of tschary
tschary

ASKER

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?
Avatar of tschary

ASKER

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.
Avatar of tschary

ASKER

Hi Vitor,

Yes. I am trying through SSMS and not from an application. I can see trigger under server objects folder.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tschary

ASKER

Thanks for the solution