How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS? 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 &gt;= DATEADD(DAY, -1, GETDATE())
    AND login_name &lt;&gt; 'prcommon';


Thanks.
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 &gt;= DATEADD(DAY, -1, GETDATE())
        AND login_name &lt;&gt; '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