0

There is any way to check all database login users list and their activities from back 3 months in SQL Server?

I tried with SQL server Logs but I need more info about user activities from past 3 months. Can we get those users list? I didn't capture any data 3 months ago.

We are facing some DB status restoring mode for some reason, so we need to capture details of users at the time who login to server.

  • Possibly unfortunately, any answer is going to start with, "First, at least three months ago, you needed to make sure you were capturing the activity you want to look at; then, you needed to make sure you retained that data for at least 3 months..." – RDFozz Jul 7 '17 at 18:40
  • If you are looking for things like Insert/Update/Delete, probably not. If you are looking for "who dropped that table" then maybe in the default traces, if you have them that far back. – Kevin3NF Jul 7 '17 at 19:06
  • One method to capture future information, you can right click SQL Server - Properties - Login auditing - change from default 'Failed logins only' to 'Both failed and successful logins'. – Wendy Jul 7 '17 at 19:37
  • @CR241 Do you have log backups for those months that you are suspecting something happened? – S4V1N Jul 7 '17 at 22:37
2

Unless you set up something (Server side trace, Profiler trace, Extended event trace, Security audit, Modify SQL Error log retention) and retain that information you will not find what you are looking for.

Based on the comments, I assume what you are looking for will be in the default trace. But unfortunately SQL Server only keeps 5 files, each 20MB. It is a fixed size buffer meaning older events get deleted.

This is a great resource to learn about default trace:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/

Starting SQL 2008 Extended event health session is running continuously unless someone turn it off.

SQL error log keeps a good deal of information. Again by default keep current and 6 older files. If you do not recycle error log and did not restart more than 6 times you might still get some information from there.

This answer by Aaron Bertrand go in details about what information is collected in these default collections.

If you need anything more than these you can always set up your own trace/audit.

| improve this answer | |
0

I don't think we can find the historical data. Only place we can look at is the default trace and sql server logs. Now again default trace gets re-used so not sure if it will have 3 months old data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.