• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

Alert mechanism if there is a particular login attempted in SQL server

Hi there,
  Is there any way i could get alert from SQL server if the particular login is attempted in SQL server?

Eg., particular User named "Robin" attempts to login SQL server , then can i setup an Alert mechanism which should send an email to me stating "Robin" has logged in to our database server.

If yes, please share me the scripts/steps to setup the same.

Note: My environment running with SQL server 2008 R2 service pack 2 enterprise edition.

thanks
Deepak
0
Deepak Kumar
Asked:
Deepak Kumar
1 Solution
 
Pawan KumarDatabase ExpertCommented:
You can create a script and their you can check the name, Name of the user you can get like...


SELECT SUSER_NAME()  

GO  

O/P

XXX\pawankhowal
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The mechanism that you want is Logon Trigger. With that you can test the login name and send an email if it matches what you want. Something like:
CREATE TRIGGER login_check ON ALL SERVER 
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'Robin' 
    EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'SQL Server',  
        @recipients = 'firstname.lastname@companyname.com',  
        @body = 'Robin just logged in SQL Server',  
        @subject = 'Logon' ; 
END;

Open in new window

0
 
Deepak KumarEngineerAuthor Commented:
Thanks Vitor for your help
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now