Send email when some users connect to SQL Server

Question

I am looking for a way to create a logon trigger in SQL Server.

Basically i need to do is when it matches some users or profiles on logon it send an alert e-mail.

I have found some things for Oracle but nothing for SQL Server.

Thanks.

Answer 1

Wouldn't it be better to store logins in a table and then provide a report for that table? Or perhaps, email a report that shows a list of logins for that day.

/* create test login */
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
GO
GRANT VIEW SERVER STATE TO login_test;
GO

USE AUDIT
GO


/* Creating audit table*/
CREATE TABLE audit.dbo.LogonAuditing
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)

GO



/* Create Login Audit Trigger */
CREATE TRIGGER connection_audit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

       DECLARE @LogonTriggerData xml,
               @EventTime datetime,
               @LoginName varchar(50),
               @ClientHost varchar(50),
               @LoginType varchar(50),
               @HostName varchar(50),
               @AppName varchar(500)

       SET @LogonTriggerData = eventdata()

       SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()

    INSERT INTO audit.dbo.LogonAuditing
     (
        SessionId,
        LogonTime,
        HostName,
        ProgramName,
        LoginName,
        ClientHost
    )
    SELECT 
        @@spid,
        @EventTime,
        @HostName,
        @AppName,
        @LoginName,
        @ClientHost


END;


/* in case you want to drop the trigger 
drop trigger connection_audit_trigger on all server
    go
*/

Answer 2

try this, as per the link in comment, you can creat a trigger and I have added some script to send mail

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' 
GO
GRANT VIEW SERVER STATE TO login_test;
GO
GO
CREATE TRIGGER mail_sending_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'test@AdventureWorks.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;
END;

for more option on sending mail

Answer 3

I decided to write something like this: https://www.mssqltips.com/sqlservertip/2595/get-alerts-for-specific-sql-server-login-failed-events/

... but for successful logins instead of failed logins.

It's fairly straight forward, but creates a bit of noise:

USE msdb
GO
DECLARE @job_id BINARY(16)
EXEC msdb.dbo.sp_add_job
     @job_name = N'Alert on login as sa'
    ,@enabled = 1
    ,@description = N'Send e-mail on WMI event'
    ,@category_name = N'[Uncategorized (Local)]'
    ,@owner_login_name = N'sa'
    ,@job_id = @job_id OUTPUT
DECLARE @cmd NVARCHAR(MAX) = N'
DECLARE @msg NVARCHAR(MAX) = ''''
SET @msg = @msg + CHAR(13) + CHAR(10) + N''HostName: $(ESCAPE_SQUOTE(WMI(HostName)))''
SET @msg = @msg + CHAR(13) + CHAR(10) + N''ApplicationName: $(ESCAPE_SQUOTE(WMI(ApplicationName)))''
EXEC msdb.dbo.sp_send_dbmail
  @recipients = ''[email@example.c]'',
  @profile_name = ''[SQLMailProfile]'',
  @body = @msg,
  @subject = ''There was an sa login ' + N'on $(ESCAPE_SQUOTE(A-SVR)).'';';
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @job_id
   ,@step_name = N'Step 1 - send e-mail'
   ,@step_id = 1
   ,@on_success_action = 1
   ,@on_fail_action = 2
   ,@subsystem = N'TSQL'
   ,@database_name = N'msdb'
   ,@command = @cmd;
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1;
EXEC msdb.dbo.sp_add_jobserver
    @job_id = @job_id
   ,@server_name = N'(local)';
DECLARE @namespace NVARCHAR(255) = N'\.\root\Microsoft\SqlServer\ServerEvents' + COALESCE(CONVERT(NVARCHAR(32), SERVERPROPERTY('InstanceName')), N'MSSQLSERVER');
EXEC msdb.dbo.sp_add_alert
    @name = N'Login as sa alert'
   ,@enabled = 1
   ,@category_name = N'[Uncategorized]'
   ,@wmi_namespace = @namespace
   ,@wmi_query = N'SELECT * FROM AUDIT_LOGIN WHERE LoginName = ''sa'''
   ,@job_id = @job_id;

Just be sure to fill in the destination email address and mail profile name. And if you want to email on more / all logins, update or remove the LoginName from the WMI query.

If there is interest, I can post the deduplicated version too.