Solved

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

Posted on 2016-10-25
3
40 Views
Last Modified: 2016-11-04
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
Comment
Question by:Deepak Kumar
3 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41859804
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
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41859913
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
 

Author Closing Comment

by:Deepak Kumar
ID: 41873608
Thanks Vitor for your help
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question