Finding out when a user last logged in

[daveonion]

Hi i need a way of finding out when 10 users last logged in using their sql servers login, is there a way?

thanks

Dave

 

[TheBugSlayer]

Hmmm, one way I can think of is running a trace on the server and capturing the Audit Login and Audit Logout events of the Security Audit event class...

If I come up with something else I will post it in here.

 

[TheBugSlayer]

And you can save the trace data into a SQL Server table directly, preferably on a different server.

 

[JamesLean]

Set the Security Audit Level of the server to audit ALL authentication events. This will automatically record successful and failed login attempts in the SQL Server log.

Right-click the server in Ent Mgr, click the Security tab and set the Audit Level in the Security group box.

Bear in mind that this can obviously grow your SQL logs quite fast if you have a busy system with lots of connections.

--James

 

[SQLBill]

But the bottom line is that there isn't any way to see the last 10 logins UNLESS you have set something up in advance to log the information.

If you do James idea, you can have a job set to run sp_cycle_errorlog once a week or every couple of days. That will create a new errorlog for you.

-SQLBill