Tracking Login Password Changes in SQL Server

By default, SQL Server does not keep track of login password changes. When the question initially came up with a user, I thought that perhaps it might be in the default trace or in the system_health extended event session. No such luck. So I was in search of an alternate way to keep track of these events, if not retroactively, at least going forward. In a short time you can be up and running with collecting password change information using three different methods: server-side trace, event notifications, and SQL Server audit. Below I will provide an example using each technology. Note that all three examples are able to track password changes using ALTER LOGIN, the system procedure sp_password (deprecated since SQL Server 2005), or the Management Studio Login properties dialog. Trace includes an event called "Audit Login Change Password Event" - which is much more reliable than capturing all batches and filtering on '%sp_password%' and '%ALTER%LOGIN%PASSWORD%'. The EventID is 107, so you can set up a very simple trace with the following code (make sure to set a proper path to the desired trace file): More...