Post reply

Audit failed/successful login LOG

  • December 19, 2019 at 2:39 pm

    #3709128

    Hello everyone (:

    I have some Audit issue on SQL Server 2008R2 version, standard edition.

    I want to Audit failed and successful login, so to do that i'm right click on the instance --> Properties -> Security, and choose the option "Both failed and successful logins", and save:

    The only problem is that he keep a lot of  successful events, and I just want to save the last month and that's it.

    How do i do it ?

    Thank's!

    Attachments:
    You must be logged in to view attached files.

  • December 19, 2019 at 3:43 pm

    #3709164

    Because where you're setting it will be tracking the information in the SQL Logs, rather than a separate log (such as using SQL Audit in Enterprise edition,) you'd have to change how long you retain your SQL Logs instead.

  • December 21, 2019 at 4:08 pm

    #3709801

    jasona.work wrote:

    Because where you're setting it will be tracking the information in the SQL Logs, rather than a separate log (such as using SQL Audit in Enterprise edition,) you'd have to change how long you retain your SQL Logs instead.

    But how?

    • This reply was modified 4 years, 4 months ago by  Nivs719.

  • December 23, 2019 at 3:42 pm

    #3709989

    In SSMS, if you right-click on the SQL Server Logs in the Management section, you can limit the number of log files before they get recycled.  Note, though, that the logs cycle when the SQL service starts, so unless you're restarting the server monthly, or manually going in to restart the service, they'll keep accumulating entries.

    Alternatively, you can "force" a log recycle using "sp_cycle_errorlog"

    • This reply was modified 4 years, 3 months ago by  jasona.work. Reason: Added some detail

  • December 23, 2019 at 3:47 pm

    #3710000

    Alternatively, stop using the errorlog to capture login events and use SQL Audit or an Extended Events session instead.  That way, you can control with fine granularity what you keep and what you don't.

    John

  • December 23, 2019 at 5:14 pm

    #3710049

    John Mitchell-245523 wrote:

    Alternatively, stop using the errorlog to capture login events and use SQL Audit or an Extended Events session instead.  That way, you can control with fine granularity what you keep and what you don't.

    John

    Except Audit isn't available in Standard Edition, which is what the OP has.

  • December 30, 2019 at 9:23 am

    #3711117

    Audit is indeed available in Standard Edition, going all the way back to (at least) SQL Server 2008 R2.  Granted, it's only for server-level events, but I think that will include failed and successful logins (although I haven't actually tried it).

    John

    • This reply was modified 4 years, 3 months ago by  John Mitchell-245523. Reason: Crossed out what I said, because it's wrong. Audit was only available in Enterprise Edition when it was first introduced

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply