Last Login Date and Time

  • I get the current SQL logon data using DMVs and one of them is using sys.dm_exec_sessions for SQL accounts. However, it does give me the information of login create date and last login date and time. My understanding is that i would loose the previous history once the server get's rebooted. Is that true? If yes, what would be the other options?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • sys.dm_exec_sessions has no "history".  It only shows "active" sessions.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Admingod wrote:

    I get the current SQL logon data using DMVs and one of them is using sys.dm_exec_sessions for SQL accounts. However, it does give me the information of login create date and last login date and time. My understanding is that i would loose the previous history once the server get's rebooted. Is that true? If yes, what would be the other options?

    Other options for what?  What are you trying to do?  Yes, everything from sys.dm_exec_sessions will be cleared when SQL Server restarts.  That's the same for many, if not all, DMVs.

    John

  • Thanks! For audit purposes, I am trying to find when was the the last login of a SQL account in databases. So if i create a new sql account and join with sys.database_role_members and database_principals to get more details.However, i am not getting the right last login details, it does give me last login when the server was last rebooted. Can this be done without server level trigger?

  • You can use an Extended Events session to capture login events, or you can audit logins in the errorlog.  Or you could periodically save the contents of sys.dm_exec_sessions, although there'd be the danger that some processes will have connected and disconnected between times, or that somebody will log in but not run any queries.  There's SQL Audit as well - you have several choices there!

    John

  • So there won't be true consistent last login then? I will need to be able to identify haven't logged in for over 120 days however, our servers are rebooted every month for updates. So the best option would be save the sys.dm_exec_sessions contents into physical table for historical information?

  • Admingod wrote:

    So there won't be true consistent last login then? I will need to be able to identify haven't logged in for over 120 days however, our servers are rebooted every month for updates. So the best option would be save the sys.dm_exec_sessions contents into physical table for historical information?

    No.  If want to guarantee that you're not going to miss anything, either setup Extended Events to capture logins or setup up a server side trace to do the same thing.  Don't forget that you'll either need to restart either one after a bounce of the SQL Service or add it to the startup parameters.

    As a bit of a sidebar, yes, you could do all of this using the SQL Server Error Log but there's no way that I'd enable logging of logins there because it'll seriously clog up the works when you're manually trying to find something in the error log.

    I'd also get out of the habit of having discrete logins even if they're Active Directory logins.  Use Active Directory Groups instead.  Of course, the exception (there's almost always an exception) to that rule is Service Logins for applications.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    I'd also get out of the habit of having discrete logins even if they're Active Directory logins.  Use Active Directory Groups instead.  Of course, the exception (there's almost always an exception) to that rule is Service Logins for applications.

    I agree with that 100%.  Just for the OP's information, even if you grant access to AD groups instead of individual users, you'll still see information for the individual users captured in your trace or XE session, or in the errorlog or sys.dm_exec_requests.

    John

  • John Mitchell-245523 wrote:

    Jeff Moden wrote:

    I'd also get out of the habit of having discrete logins even if they're Active Directory logins.  Use Active Directory Groups instead.  Of course, the exception (there's almost always an exception) to that rule is Service Logins for applications.

    I agree with that 100%.  Just for the OP's information, even if you grant access to AD groups instead of individual users, you'll still see information for the individual users captured in your trace or XE session, or in the errorlog or sys.dm_exec_requests.

    John

    And, just to be sure, that's a good thing. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for the info. Actually i would need to capture the information of logins which are not logged in over 120 days and build a query so someone would run the query once in a while to find the logins which are not logged in over 120 days. If this is through extended events or error logs then i don't think i can have some one run reports against it right?

  • I won't say that you absolutely can't, but it would be a lot easier to pull it all into a table daily (or whatever frequency works best for you) for easy querying.

    John

  • So periodically save the contents of sys.dm_exec_sessions to a table would be a good option?

  • As I explained earlier, if you do that connections that started and finished between savings won't be captured.  That's why it's better to use Audit, XE, trace, eventlog or whatever persistable solution works for you.

    John

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply