In SQL Server, I see there's a field in syslogins titled "updatedate" which is a timestamp of the last time the login was updated.
- What constitutes an "update"?
- My goal is to only get a timestamp of the last time a password was changed.
Securitysql server
In SQL Server, I see there's a field in syslogins titled "updatedate" which is a timestamp of the last time the login was updated.
Best Answer
SQL Server does not track this information by default, so you will not be able to look back and see when these happened in the past. The trace event is 107 and it is not in the default trace (and I don't see anything in the system_health extended event session either).
For the future, you can set up an audit specification (no idea if this is an option for you because you didn't specify version or edition). You may also be able to capture these events using a server-side trace or an event notification on ALTER LOGIN.
Server-Side Trace
Here is a script that will produce a server-side trace that captures just the
Audit Login Change Password Event
(and this works for bothALTER LOGIN
andsp_password
) - please be sure to update the path in the firstEXEC
line:Later when you want to review the events the trace captured, you can see the last update time for each login with the following query:
You'll probably want to schedule this query periodically and snapshot the results somewhere, since the data won't hang around in the default trace forever.
Event Notifications
If you'd rather have an EVENT NOTIFICATION instead of a server-side trace running, you can do it this way. First, create a table in MSDB to track the login name and the time of the change (you may want to capture other information from the event as well, like I did with the trace above, but this is a bit more involved and I'm trying to maintain some brevity).
Now, we need a queue and a service for our notification:
And a procedure to actually do something when the event happens:
And finally set the notification to use this procedure.
Now change the password for a few logins, and you should see results from the following query: