1

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.

1

3 Answers 3

2

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
*/
1

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 = '[email protected]',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;
END;

for more option on sending mail

0

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 protected]]'',
  @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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.