2

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"

  • 1
    No, unless you are going to programmatically restart the service as well. The setting will not take affect until a restart of the service. reference – Shawn Melton Dec 3 '14 at 16:00
2

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?

  • Yeap, I'm restarting the server. It's getting some spurious login failures whilst it's getting back up to speed – SteveC Dec 3 '14 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.