14,494 questions
Hi,
Please check:
Viewing SQL Server Audit Logs
How to analyze and read SQL Server Audit information
===============================================
If the response helped, do "Accept Answer" and upvote it.
SQL Server Login Auditing
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 37%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
Gav
1
Reputation point
Hi,
Is there a way to check the active audit level setting on a SQL Server since it can be changed multiple times without restarting the service.
This is Login Auditing in Server Properties. Via GUI or reading the registry tells me what is set but not what is active.
thanks
SQL Server Other
3 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 78%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECZ%3C/text%3E%3C/svg%3E)
Cris Zhan-MSFT 6,661 Reputation points2020-08-27T05:48:11.387+00:00 -
Gav 1 Reputation point
2020-08-27T09:05:44.473+00:00 Thanks for the response but these do not relate to my question.
I'm interested in this setting (path will change depending on version):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\AuditLevel
I can change it to:
0 = None
1 = Failed Logins Only
2 = Successful Logins Only
3 = Both failed and successful logins
but that will not become active until the service is restarted.
If I change it multiple times, is there anywhere in the configuration that will tell me the active setting (before a restart). For example it may be set to 3, the registry and GUI show me that, but the active setting is 1 because the service has not been restarted. I would like to know if this active setting is held somewhere.
Thanks,
Sign in to comment -
-
Cris Zhan-MSFT 6,661 Reputation points
2020-08-28T08:06:25.91+00:00 Hi,
Thanks for your reply. Sorry for my misunderstanding.
I tested this ,It is true that the changes made to this setting in the GUI or registry will not work before the SQL Server services restart. The login information recorded in the Errorlog is still based on the settings at this startup.(And the 1 = Successful Logins Only.)
The active setting --- Maybe it can be judged from Errorlog .
The setting for login auditing is actually a registry entry and SQL Server only reads this setting when the SQL Server service starts up
===============================================
If the response helped, do "Accept Answer" and upvote it.-
Cris Zhan-MSFT 6,661 Reputation points
2020-08-31T01:32:42.077+00:00 Hi,
Is there any update on this case?
Sign in to comment -
-
Gav 1 Reputation point
2020-09-11T09:17:50.14+00:00 Hi,
Thanks for the response.
If it can only be judged from the ErrorLog, maybe a possible future feature request for this functionality?