As a SQL Server user, you may have come across the term “UpdateLoginStats” while monitoring your server’s activity. In this blog post, we will explore what UpdateLoginStats is and how it affects your SQL Server environment.

During a stress test, you may have noticed that for every login, there is an additional SQL transaction with the ObjectName “UpdateLoginStats” appearing before the “Audit Login” transaction. At first, you might think it’s a logon trigger that you created, but upon further investigation, you realize that there is no such trigger defined.

So, what is UpdateLoginStats and what does it do? When the “Common Criteria Compliance” feature is enabled in SQL Server, the UpdateLoginStats option is turned on. This option updates login statistics for every login request. It also enables additional security logging, which can be viewed by querying the sys.dm_exec_sessions dynamic management view.

To view the login statistics, you can execute the following query:

SELECT session_id, original_login_name, last_successful_logon, last_unsuccessful_logon, unsuccessful_logons
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

By enabling the “Common Criteria Compliance” feature, you can track login activity and gather valuable information about successful and unsuccessful logins.

If you want to disable the UpdateLoginStats option, you can use the following commands:

EXEC sys.sp_configure N'common criteria compliance enabled', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Once disabled, you can capture the profiler again, and you will notice that there are no additional transactions before the login. The columns related to login statistics will also be NULL.

It’s important to note that exploring and understanding these features and options can sometimes require breaking things and putting them back together. By experimenting and investigating, you can gain a deeper understanding of SQL Server’s internals.

Lastly, in the screenshot provided in the example article, there is another transaction called “CSECAuthMedAccess::InitReadWrite” mentioned. Unfortunately, the article does not provide further information about this transaction. If you have encountered it and have insights into its internals, we would love to learn from you.

Stay tuned for more SQL Server tips and insights!