I have been asked to give report of successful and unsuccessful database users login based on database since last 30 days for Audit purpose. But unfortunately we have not had enabled the audit option on the server property and trace as well before. But still we need to provide the report now, is it possible? If so how can we achieve it?
Thanks in Advance
If you have not set up Auditing before, you cannot get the data from the past.
Most servers have set up Logging of FAILED Logins at Server level, and depeding on your SQL Server Error-Log history, you can get those from there.
Logon always happens at Server Level - unless you are using contained databases (http://technet.microsoft.com/en-us/library/ff929071.aspx).
Therefore you can only capture it at Server Level.
For a contained database you would have to use the Auditing feature. - http://technet.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx
For all others there is no way to get such an event. You could imitate it by looking for shared locks at database level (using extended events), but still, there is no way to get it back from the past.
- Edited by Andreas.WolterMVP Friday, November 15, 2013 10:53 AM links
Logon always happens at Server Level - unless you are using contained databasesAnd contained databases are not available on SQL 2005.
If someone at when logging in specified a database to which he did not have access, this will be recoerded as a login failure.
But if someone who is already logged in, attempts to access a database to which he does not have access, I don't believe this is recorded at all. At least I can't see any trace event. But maybe this is available in SQL Server Audit, but again that feature is not available in SQL 2005.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
totally true - in the midst of writing and while making sure not to forget about exceptions, I forgot about the title, which implied 2005
It might be useful for other users though, so I leave it unchanged.
To continue from what Erland added:
There is in fact no special event for accessing a database. Failures are also not logged because they are not critical.
As extended events are not available, your only alternative to the server settings is SQL Trace (Profiler)
Yet without a special event, you still have to revert to the alternative I mentioned: Trace Shared Locks on Database level.
In all cases: no history.
You can try the below tool and then analyse the logs if the transaction logs are available on the server.