Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum login attempts being reached. This query will also be customized for After Hours Login.

I already know that some of data requested can be generated by using SQL Profiler or C2 audit, Event logs and the sys.login_token, sp_who, etc.

However, I cannot get to the SQL script that would allow me to run the reports at any given time.

share|improve this question
1  
What version and edition are you using? Have you looked into SQL Server Audit? In SQL Server 2012 the licensing restrictions were loosened significantly so that you can perform auditing of most basic server-level events in all editions. –  Aaron Bertrand May 23 '13 at 13:52
    
I'm using SQL Server 2008, R.2. –  user2413810 May 23 '13 at 17:19
    
Standard, Express, Enterprise, ...? –  Aaron Bertrand May 23 '13 at 17:20
    
Enterprise Edition –  user2413810 May 23 '13 at 19:10
    
SQL Server will only be able to log failed logins, successful logins and both failed and successful logins in the event log. Windows will be able to log Account locked along with the ones I mentioned before. To audit Create/Delete account or account unlock, you might want to consider SCOM or powershell -- blogs.technet.com/b/mazenahmed/archive/2012/02/19/…. A good auditing solution is also well described at colleenmorrow.com/2012/07/31/… –  Kin May 23 '13 at 19:54
add comment

closed as off-topic by Max Vernon, Mark Storey-Smith, Paul White, Kin, bluefeet Sep 30 '13 at 13:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Mark Storey-Smith, Paul White, Kin, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.