• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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

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.

1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now