Programmatically Enable Auditing on Logins in SQL Server

The xp_instance_regwrite extended stored procedure helps you alter registry entries. To enable both success and failure login auditing, use the following command:

USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', _
N'Software\Microsoft\MSSQLServer\MSSQLServer',_
N'AuditLevel', REG_DWORD, 3

To disable login auditing

USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', _
N'Software\Microsoft\MSSQLServer\MSSQLServer',_
N'AuditLevel', REG_DWORD, 0

Login auditing is important as it helps you identify the user who performed unwanted actions, when they occur.

Note: When I ran the query, I got the message “0 rows affected”. However when you open the registry, the entry is what is expected to be, after running the query.