Last Login Date and Time

Question

Post reply

Last Login Date and Time

Admingod

SSCertifiable

Points: 5997
More actions
November 28, 2019 at 12:14 am

#3701700

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?

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

Admingod SSCertifiable Points: 5997 More actions · Answer 4

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?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

Admingod SSCertifiable Points: 5997 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Admingod SSCertifiable Points: 5997 More actions · Answer 10

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?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

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

bhanumurthy Newbie Points: 5 More actions · Answer 12

please follow the below link..

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/78d547cd-03b0-4ecb-96d8-c86fa95265cf/find-the-login-history-for-last-one-month-in-sql-server?forum=sqlsecurity

Admingod SSCertifiable Points: 5997 More actions · Answer 13

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

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