Send email when some users connect to SQL Server

Posted on

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 :

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

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

Leave a Reply

Your email address will not be published.