SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Login Auditing

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'Software\Microsoft\MSSQLServer\MSSQLServer',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.

Tim Radney - Database Professional

Tim Radney - Database Professional by Tim Radney I am a Sr DBA for a top 40 US bank. I live in the south eastern US. I have been working with database since 1999 but only full time for the past three years.


Leave a comment on the original post [timradney.com, opens in a new window]

Loading comments...