May 31, 2022 by Kenneth Fisher
I little while back I presented at Pass Data Community Summit. Specifically the presentation was Auditing your data and data access and as with each of the presentations I’ve done I not only learned a few things but I clarified something that I’d understood for a while, but didn’t really understand that I understood. In this particular case, there is a auditing mindset.
This is the idea that you can pull metadata information from all over the place. Want to know when a database was last used but don’t have an extended events session in place? Check for audit columns (i.e. a column that tells you when the row was created). It’s not perfect, but it might get you close. Or how about the query cache? You can mine that for any non-maintenance queries applied to a database. Want to know when a user last logged in? The error log can give you that!
SQL Server has information all over the place. No, there isn’t an audit automatically created to tell you the last time Bob queried the table ISawASquirrel but with some creative use of the information you do have available you might be able to give the person asking the question some sort of answer. And a lot of times they are happier with that than nothing.