In recent months I have been asked to ensure we are auditing both successful and failed logins, not just failed logins. It is simple enough to open SSMS, connect to the instance, right click on the server, chose properties, click on security and then check the proper radio button. When putting together or most recent updates to our server installation guide I decided to spend the few minutes to research how to make this change with few steps.
In my case I need this to be both failed and successful logins. I will execute the following code within SSMS.
EXEC xp_instance_regwriteN'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer',N'AuditLevel', REG_DWORD, 3
Notice I am updating the registry and setting the AuditLevel to a 3. What are the other options?
None = 0
Successful Logins Only = 1
Failed Logins Only = 2
Both Failed and Successful Logins = 3
Here is a visual as well.