0

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.

4
  • May be this helps: dba.stackexchange.com/a/54501/110455
    – McNets
    Commented May 29, 2017 at 7:42
  • @McNets without any sql trace i just need the email notification wherever user is login into the machine through SQL SERVER authentication. Commented May 29, 2017 at 8:12
  • 1
    I would think you could wrap your query (with some minor modifications) in with a server logon trigger - dba.stackexchange.com/a/76215/60085 Commented May 29, 2017 at 9: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. Commented May 29, 2017 at 9:47

1 Answer 1

1

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 = '[email protected]',
                            @body = 'Customise your code',
                            @subject = 'New User Login With Sql Server Authentication';
END 

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.