question

muk avatar image
muk asked

find out which login last accessed db

Hi all, I was wondering is there any t-sql query to figure out the last login to have accessed a database. I have a few databases that no one knows what or who is accessing them. It says the date last used is today because I run backups everyday. I am trying to figure out if there are any applications using this database. Thanks.
t-sqlsql-server-2008-r2sql-serverqueryperformance
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
The best way to do this would be to use extended events to capture logins so that you know precisely who is connecting to what. You can also add extended events to capture what queries they're running. But, you have to have it set up in order to capture this. Nothing built-in to the system automatically captures this type of information. However, you do have the option of looking at the cache to see what queries have been run, but not who ran them. You can look at sys.dm_exec_query_stats to see an aggregate of attributes about queries that are currently in cache. This won't do what you need, but it's an additional investigative tool you can use until you get the extended events set up.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
select * from sys.traces then open the trace file in the profiler and check for login details in it
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But if the trace doesn't capture the right data, what's the point? The blackbox trace is running all the time, but it doesn't capture all transactions or even all logins. So you can't get anything from it. You would have to set something up.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.