locked
Users or Login's Last Access Date/Time RRS feed

  • Question

  • Hello,

    I would like a query that provides results of ALL the database users and logins (the authentication is set to mixed mode and so, there are both sql server logins and database users and AD groups and AD users as well) that have not accessed the concerned sql server instance or the concerned database/s during the last xx number of days (say 90 days). 

    We have SQL servers 2008, 2012, 2014 and 2016.

    Will greatly appreciate your quick, clear response.

    Thanks.

    Victor


    Victor

    Wednesday, September 26, 2018 10:37 PM

All replies

  • Hi Victor,

     

    You can use the command to query the last login time of current SQL Server logins.

     

    SELECT MAX(login_time) AS [Last Login Time], login_name [Login]

    FROM sys.dm_exec_sessions

    GROUP BY login_name;

     

    However you need to pay attention that it just search the current session. And if your service is restarted or the session is cleaned, the information is not complete. From your description, I knew that you want to query the logins which haven't accessed during the last 90 days. So it may not satisfy your requirement.

     

    As a result, I would suggest you creating a SQL Server Login trigger to record an entry every time a user logins into a table.

     

    For example: firstly creating a table that will store the login information.

     

    USE TEST

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ServerLogonHistory](

     [SystemUser] [varchar](512) NULL,

     [HostName] [varchar](512) NULL,

     [DBUser] [varchar](512) NULL,

     [SPID] [int] NULL,

     [LogonTime] [datetime] NULL,

     [AppName] [varchar](512) NULL,

     [DatabaseName] [varchar](512) NULL

    ) ON [PRIMARY]

    GO

     

    Next, creating the logon trigger:

     

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Connections]

    ON ALL SERVER WITH EXECUTE AS 'sa'

    FOR LOGON

    AS

    BEGIN

    INSERT INTO ServerLogonHistory

    SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()

    END

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [Connections] ON ALL SERVER

    GO

     

    Then you could see the login records in the ServerLogonHistory like the following screenshot. And you can compare the logins in "ServerLogonHistory" and "sys.server_principals" to find the logins that have not accessed during last 90 days.

     

     

     

    Best Regards,

    Emily


    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


    Thursday, September 27, 2018 3:23 AM
  • Note that the trigger suggested by Emily will work to determine whether the user has accessed the server in 90 days, but it does not track database usage, as a user could switch database during a session. This can possibly be captured with SQL Audit.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, September 27, 2018 9:28 PM
  • Emily,

    Thanks for the input. I am not too concerned about the session. If a user has not logged in during the past 90 days or so, then, we will assume that the concerned user is a dormant user or we have other ways to find out why that user might not have accessed the database. So, basically, I am looking for one query that can pull last login info of all the databases including sql server connection. Thanks.

    Victor


    Victor

    Thursday, September 27, 2018 9:47 PM
  • What Emily means by 'session' is that the first query queries a DMV and the results returned are from the current active workload. Since you need something from past 90 days, that query may not satisfy your need. 

    You would have to set up some sort of auditing and leave it running for 90 days and analyze the results to see which user accounts are active and which ones aren't. Extended Events, Server Side trace, Database Audit are some of the built-in auditing tools that would meet your requirement.

    I posted the following server-side trace on a similar thread for auditing user activity. You can use it too for identifying the active users. 

    • You can add/remove/edit the script to add more filters/events etc as per your need. 

      The following trace example captures the schema object access event on any database on the instance. It captures the hostname, database name, login name, Query text etc. of the clients accessing this instance. After the trace is run, you will see trace files starting to populate in the location given. You can go to that location and read the files from there. 

      Make sure to replace values wherever needed to reflect your environment. 

    DECLARE @TraceID INT 
    DECLARE @MaxSize bigint  
    DECLARE @Filename nvarchar(256)
    DECLARE @Path nvarchar(256)
    DECLARE @DBName nvarchar(100)
    
    set @TraceID = 2
    set @MaxSize = 5000 --MB
    set @Filename = '_AppLoginAuditTraceFile'+ convert(varchar(27),GETDATE(),112) + 
    
    convert(varchar(2),DATEPART(mi,getdate())) + convert(varchar(2),DATEPART(ss,getdate())) 
    set @Path = 'D:\xyz\' --replace with yours
    set @Filename  = @path + @Filename  
    
    DECLARE @on BIT, @tid INT  SELECT @on=1
    
     EXECUTE sp_trace_create @TraceID = @TraceID output,
     @options = 2,  --It will create new file with _1
     @tracefile = @Filename,
     @maxfilesize  = @MaxSize     
    
    
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 35, @on = @on    
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 8, @on = @on    
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 11, @on = @on    
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 34, @on = @on
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 1, @on = @on    
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 10, @on = @on
    EXEC sp_trace_setevent @TraceID = @TraceID ,   @eventid = 114, @columnid = 45, @on = @on
    
    --Filter connections to capture only the required login activities
    --EXEC sp_trace_setfilter @TraceID = @TraceID,   @columnid = 11, @logical_operator = 0,   
    
    @comparison_operator = 0, @value = N'YourApplicationLoginName'
    
    --starting the trace
    EXEC sp_trace_setstatus @TraceID , 1
    
    --stopping the running trace
    --EXEC sp_trace_setstatus @TraceID , 0 --To stop the trace
    



    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Friday, September 28, 2018 12:25 AM