How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS?

Question

I need to trace on SQL SERVER authenticated user whenever he/she is logging in to the system, automatically send alert mail like this user is login with this IP and this time.

I have just one solution for this problem but this is not so proper because i have to see that manually.

Below solution is not work for alert mail because i have to run manually.

SELECT 
   DISTINCT
    @@SERVERNAME ServerName ,
    DB_NAME(database_id) DatabaseName ,
    s.login_name ,
    s.[host_name] ,
    c.client_net_address ,
    c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
    AND program_name IN ( 'Microsoft SQL Server Management Studio',
                          'Microsoft SQL Server Management Studio - Query' )
    AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
    AND login_name <> 'prcommon';

Thanks.

May be this helps: https://dba.stackexchange.com/a/54501/110455 — McNets, May 29 '17 at 07:42
@McNets without any sql trace i just need the email notification wherever user is login into the machine through SQL SERVER authentication. — Ankit Bhalala, May 29 '17 at 08:12
I would think you could wrap your query (with some minor modifications) in with a server logon trigger - https://dba.stackexchange.com/a/76215/60085 — Scott Hodgin - Retired, May 29 '17 at 09:38
@ScottHodgin I used that logon trigger it may not work properly because once trigger is created after that no one can login in to the machine. And generate error of trigger execution. — Ankit Bhalala, May 29 '17 at 09:47

score 1 · Accepted Answer · answered Jun 01 '17 at 05:19

Here I got the answer of this question:

First i made on Table for storing history of SqlServerLoginHistory:

CREATE TABLE AuditDetail
(
ServerName VARCHAR(255),
DatabaseName VARCHAR(255),
LoginName VARCHAR(255),
HostName VARCHAR(255),
ClientNetAddress VARCHAR(255),
ConnectionTime DATETIME2
)

After that made one logon trigger.

CREATE TRIGGER [connection_limit_trigger]
ON ALL SERVER
FOR LOGON 
AS
BEGIN
INSERT INTO AuditDetail
SELECT 
       DISTINCT
        @@SERVERNAME ServerName ,
        DB_NAME(database_id) DatabaseName ,
        s.login_name ,
        s.[host_name] ,
        c.client_net_address ,
        c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
        AND program_name IN ( 'Microsoft SQL Server Management Studio',
                              'Microsoft SQL Server Management Studio - Query' )
        AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
        AND login_name <> 'prcommon' and not exists (select 1 from Auditdetail where auditdetail.connectiontime = c.connect_time);

END;
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER  
GO

After that made on Simple trigger (after insert) on AuditDetail table to send alert mail .

CREATE TRIGGER auditdetailtrigger
ON AuditDetail
AFTER INSERT
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Admin',
                            @recipients = 'abhalala@stackoverflow.com',
                            @body = 'Customise your code',
                            @subject = 'New User Login With Sql Server Authentication';
END

How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS?

1 Answers1