How can I enable audit logging on multiple SQL 2005 servers at once?

Posted on 2009-12-17
Last Modified: 2012-05-08
Id like to enable auditing of both failed and successful logins on multiple systems running Microsoft SQL 2005 Express.
I know its easy to do via the Management Studio.  I just go into the server properties, choose security, and select Both failed and successful logins
Since I have over 100 systems I need to modify, Im looking for some automated way to do it.  Be it a command or a registry entry, just something I can push down without having to individually go into each server.

Question by:ntefft
    1 Comment
    LVL 57

    Accepted Solution

    Well, you are aware of enabling Security Auditing in SSMS.

    And the T-SQL Script equivalent is given below

    sp_configure 'show advanced options', 1 ;
    sp_configure 'c2 audit mode', 1 ;

    You can store this in a sql file and then use SQLCMD to run this in all servers from a single server..

    Note: If you are using Windows Authentication, then use Trusted Connection -E in SQLCMD else you need to pass it using individual credentials again( manually)..

    No other easier workaround to implement your requirement..

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now