none
login detail RRS feed

  • Question

  • Hi,

     

     Is there any way to get last login date and time  for sql logins  via TSQL,


    please help
    Monday, August 14, 2017 6:54 AM

All replies

  • Hi,

     

     Is there any way to get last login date and time  for sql logins  via TSQL,


    please help

    Dharam,

    There are lot of similar threads to the question you have asked, I would request you to search online for small question 

    Last Login details

    I would personally choose a logon trigger.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, August 14, 2017 7:29 AM
    Moderator
  • You can use the following script:

    SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
    FROM sys.dm_exec_sessions
    GROUP BY login_name;

    But that only works for the users currently logged in, not particularly interesting.

    Monday, August 14, 2017 8:29 AM
  • yes you can have, if you enabled default trace in your sql server

    For the above You can monitor using below query with using SQL Server Default traces by filtering hostname, login name by adding them in where condition.

    DECLARE @path NVARCHAR(260)

    SELECT @path=path FROM sys.traces WHERE is_default = 1

    SELECT TE.name AS EventName, DT.DatabaseName, DT.ApplicationName, DT.HostName
    DT.LoginName, COUNT(*) AS Quantity 
    FROM dbo.fn_trace_gettable (@path,  DEFAULT) DT 
    INNER JOIN sys.trace_events TE 
    ON DT.EventClass = TE.trace_event_id 
    GROUP BY TE.name , DT.DatabaseName , DT.ApplicationName, DT.LoginName, DT.HostName
    ORDER BY TE.name, DT.DatabaseName , DT.ApplicationName, DT.LoginName, DT.HostName

    Please find the below URL's

    https://www.red-gate.com/simple-talk/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/

    https://www.mssqltips.com/sqlservertip/3445/using-the-sql-server-default-trace-to-audit-events/


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Monday, August 14, 2017 8:50 AM
  • Hi Dharm_yadav,

    >>Is there any way to get last login date and time  for sql logins  via TSQL,

    If I remember this correctly, SQL Server does not keep track of login’s last login date/time, your best option is to setup an audit for SUCCESSFUL_LOGIN_GROUP, then you can find the most recent login date/time for a specific login. A little POC here:
    ----Create Audit
    
    USE [master]
    
    GO
    
    CREATE SERVER AUDIT [loginaudit]
    TO FILE 
    (	FILEPATH = N'c:\'
    	,MAXSIZE = 0 MB
    	,MAX_ROLLOVER_FILES = 2147483647
    	,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	QUEUE_DELAY = 1000
    	,ON_FAILURE = CONTINUE
    )
    
    GO
    
    ----Create audit specification
    
    USE [master]
    
    GO
    
    CREATE SERVER AUDIT SPECIFICATION [loginauditspec]
    FOR SERVER AUDIT [loginaudit]
    ADD (SUCCESSFUL_LOGIN_GROUP)
    
    GO
    
    ----Find login’s last login time
    
    USE master;
    DECLARE @path varchar(max);
    SELECT
    	@path = (SELECT
    			log_file_path + name + '*'
    		FROM sys.server_file_audits
    		WHERE name = 'loginaudit');
    SELECT 
    	server_principal_name
       ,MAX(event_time) AS last_login_datetime
    FROM fn_get_audit_file(@path, NULL, NULL)
    WHERE action_id = 'LGIS'
    GROUP BY server_principal_name;





    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, August 15, 2017 9:44 AM
    Moderator