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
tscharyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

tscharyAuthor Commented:
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')
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange.
How are you logging in?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

tscharyAuthor Commented:
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;
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
tscharyAuthor Commented:
Hi Vitor,

Yes. I am trying through SSMS and not from an application. I can see trigger under server objects folder.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunaly is how SSMS works. Every refresh fires a login trigger. I think that you pretend to register all 'sa' logins, even from SSMS, right?
If not then you can always add a filter in the trigger:
CREATE TRIGGER [testSA]
 ON ALL SERVER 
 FOR LOGON
 AS
 BEGIN
 IF ORIGINAL_LOGIN()= 'sa' AND APP_NAME() NOT LIKE 'Microsoft SQL Server Management Studio%'
     INSERT INTO [tempdb].[dbo].[TestSA]([Value])VALUES ('tsc') '
END

Open in new window

Other validation that you can do is to check if the record exists. If so, don't insert it anymore.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tscharyAuthor Commented:
Thanks for the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.