SQL Server 2008 R2 – How to Change Login Audit Settings Programmatically

auditloginssql serversql-server-2008-r2

Is it possible to change the login auditing settings of a SQL Server database by script, rather than through the Server Properties Security setting in the SSMS UI?

I need to programatically change the setting to "None", and then back to "Failed logins only"

Best Answer

Sure, here are the commands listed below. (Reformatted from original post for reading ease.)

These can derived from clicking on Server Properties, then choosing the Security tab and changing the Login auditing settings and scripting out the code, instead of clicking on the OK button to close the dialog. This type of scripting is usually an option on actions taken through the SSMS interface. 

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer', 
   N'AuditLevel', REG_DWORD, 0 -- None
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer', 
   N'AuditLevel', REG_DWORD, 1 -- Successful Only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer', 
   N'AuditLevel', REG_DWORD, 2 -- Failed only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer', 
   N'AuditLevel', REG_DWORD, 3 -- Failed & Successful
GO

So, for your requirement just set the key to 0 (no auditing) then back to 2 (failed only).

But, alas, as Shawn Melton pointed out, it requires the server to be restarted before the change can be applied. Of course, you can program for restarting the server as well. Does allowing a restart (or 2 restarts) fit into your process?

Related Question