When was Windows Domain Account last used?

Question

When was Windows Domain Account last used?

rew-370421

Hall of Fame

Points: 3640
More actions
April 23, 2010 at 10:18 am

#231981

I found the query on how to determine when a SQL2005 ID account was last used.
My question is how can I determine when a Windows Domain account was last used, that is authorized to access a database? When was the last time the Windows User signed onto the SQL2005 server?
...thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

There isn't a query to show when any account was last used, even a SQL Server-based one. Can you post that? As far as when a Windows account last logged on to the domain, that's a bit more tricky. You literally have to query every domain controller and get a last logon date and take the newest. Beyond that, you're not going to see anything in SQL Server unless you're auditing for logins and your parsing the SQL Server error log, using a logon trigger, etc.

K. Brian Kelley
@kbriankelley

rew-370421 Hall of Fame Points: 3640 More actions · Answer 2

I found this:

http://www.sqlservercentral.com/Forums/Topic641210-359-1.aspx?Highlight=last+login

I know the User is a valid Domain User. I just need to know the last time they've accessed the SQL Server. I'd like to clean up the INACTIVE accounts.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

Right, I commented on that thread. If you follow it through to the end, you'll see that SQL Server doesn't store this information. You have to audit it using other means.

K. Brian Kelley
@kbriankelley

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

Ok. Thanks for that information. Back to square one.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

you can opt for C2 auditing although it is resource consuming as it's capturing all the database activity hence need to use only when high security is concern.

Some of the articles, which I found useful are

http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 6

Bhuvnesh (4/25/2010)
you can opt for C2 auditing although it is resource consuming as it's capturing all the database activity hence need to use only when high security is concern.
Some of the articles, which I found useful are
http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

You're better off choosing one of the following options:

- Logon Triggers (SQL Server 2005 SP2 and above)

- Extended Events (SQL Server 2008)

- Server-side trace (SQL Server 7 and above)

- Audit Login Success/Failure w/ appropriate Application Event Log reads (SQL Server 7 and above)

All of these are far less intensive than turning on C2 auditing. But they (along with C2 auditing) only report from the point they are turned on. They will not report when an event was last used prior to any of these being activated.

K. Brian Kelley
@kbriankelley