Finding last user to login to database?

[Ovatvvon]

Is there a way to find out who the most recent user was to log into a particular database?


-Ovatvvon :-Q

 

[mutley1]

Assuming the person is still logged in you could use current activity from Enterprise manager, sort by DB then check the login times. Ignore system accounts it you're looking for actual users.....

 

[Ovatvvon]

Is there any system table that I could just query to retrieve the latest login?

(Even if it has to be only if they are still logged in? Although it'd be even better if it wouldn't matter if they were still logged in or not).

Perhaps there is a 'Current Activity' system table?

-Ovatvvon :-Q

 

[Ovatvvon]

Sorry, let me clarify a bit more. We have a database out there that has no activity right now, and we'd like to see if there have been connections to it from users recently, or if it has just been laying dormant out there for months...or more.

Is there any way to see when the last user login / connection was? Any system table (or other method) that would keep track of that? (Note: any listings of current activity will be irrelevant in our situation.)

Thanks!


-Ovatvvon :-Q

 

[Catadmin]

The only way I know of to monitor database connections is to run a Profiler Trace and watch for activity or use a network sniffer. There is no place where "recent logins" are logged to SQL Server unless someone custom designed an audit table set up.

Sorry.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

Catadmin's right. Nothing holds historic login / logouts.

If you want to monitor it for a week or something, set up a profiler and writ it ti a table, just for event class "security audit" with sub class "audit login" or "audit logout" and specify the DBID in the filter.

Catadmin - might be silly but never tried it. When you have audit level set to success in the server properties under security, does that include sa / the SQL agent account etc? Just curious.....If they are pretty sure it's unused, then that would also say "Hi - I just logged in!" but if it includes system accounts then the mailbox might get flodded!!


Cheers,

M.

 

[Catadmin]

SQL Server 2005 does automatically audit the SQL Server Agent when you choose Log Audit and Existing Connection. You can monitor Broker also, but that is a specific choice. Not sure about SQL Server 2000, but I think it monitors SQL Server Agent automatically.

Also, not sure about the SA login, but I'd assume that's automatic too unless you choose to ignore system events.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"