Tuesday, May 01, 2012 5:59 PM
Is there a way in SQL Server to see what are the applications used historically to access the databases? I can see the current state using activity monitor. I want to see what applications used, lets say in last 2 months?
Thanks for your help.
Tuesday, May 01, 2012 6:16 PMAnswerer
Unless you are capturing and storing that information somewhere, there is no way to get that data as SQL Server to my knowledge doesn't store that. I'm not sure how the current application is captured, but I can say that capturing using sys.dm_exec_sessions and the program_name column may not give you accurate (or any!) results and can be easily spoofed.
Sean Gallardy, MCC | Blog
Tuesday, May 01, 2012 6:24 PMYou would have needed to be proactive on this one. There are a couple of different ways that you can implement this. You can do it through SQL Auditing, Logon Triggers, Trace, or some others that escape me at the moment. The point is, you will have to set it up before you need to look into the past.