Post reply

Login Auditing - Both failed & successful logins

  • June 8, 2011 at 7:19 am

    #389689

    We have a requirement to enable this feature across our SQL estate. Does anyone know how to turn this feature on by using tsql? We have 70+ sql instances and rather than connect to each instance I would much prefer to run a query against each instance and then restart the servers at our leisure.

    Any ideas?

    Thanks.

  • June 8, 2011 at 9:10 am

    #1335723

    this will get you your value

    xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'

    and this will change it

    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3

    there is also a new dmv in denali and 2008 r2 sp1 that will show you the values in the registry: sys.dm_server_registry

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • June 9, 2011 at 4:09 am

    #1336141

    Many Thanks Bob!

  • June 9, 2011 at 4:30 am

    #1336154

    C2 audit will be able to do this.

    and this query will help

    EXEC sys.sp_configure N'c2 audit mode', N'1'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    ----------
    Ashish

  • June 9, 2011 at 5:25 am

    #1336181

    Thanks Ashish also...

    Don't really want to enable C2 at the moment as I understand there would be a significant performance hit in doing so.

    We will be moving to 2008R2 later this year and will be looking closely at CCC.

    Cheers,

    Mark

Viewing 5 posts - 1 through 4 (of 4 total)

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