When was the syslogin's password last updated?

Question

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.

Answer 1

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 both ALTER LOGIN and sp_password) - please be sure to update the path in the first EXEC line:

DECLARE @TraceID INT, @mfs BIGINT;
SET @mfs = 5;

EXEC sp_trace_create @TraceID OUTPUT, 0, N'C:\path\tracefile', @mfs, NULL; 

EXEC sp_trace_setevent @TraceID,107,1, 1;
EXEC sp_trace_setevent @TraceID,107,11,1;
EXEC sp_trace_setevent @TraceID,107,8, 1;
EXEC sp_trace_setevent @TraceID,107,12,1;
EXEC sp_trace_setevent @TraceID,107,14,1;
EXEC sp_trace_setevent @TraceID,107,40,1;
EXEC sp_trace_setevent @TraceID,107,42,1;

EXEC sp_trace_setstatus @TraceID, 1;

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:

DECLARE @path NVARCHAR(260);

SELECT @path = [path]
FROM    sys.traces
WHERE  id = <traceID from above>;

SELECT TargetLoginName, LastTimeStamp = MAX(StartTime)
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 107 -- in case you've added other events
  GROUP BY TargetLoginName;

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).

USE [msdb];
GO

CREATE TABLE dbo.PasswordChanges
(
    LoginName     SYSNAME,
    EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now, we need a queue and a service for our notification:

CREATE QUEUE PasswordChangeQueue;
GO

CREATE SERVICE PasswordChangeService
    ON QUEUE PasswordChangeQueue 
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And a procedure to actually do something when the event happens:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message_body XML;

   WHILE (1 = 1)
   BEGIN
       WAITFOR ( RECEIVE TOP(1) @message_body = message_body
          FROM dbo.PasswordChangeQueue), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChanges(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
   END
END
GO

And finally set the notification to use this procedure.

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT LoginName, LastChange = MAX(EventDateTime)
  FROM dbo.PasswordChanges
  GROUP BY LoginName;

Answer 2

You could use LOGINPROPERTY:

-- Last password changed time for the login TestLogin
USE [master];
GO
SELECT LOGINPROPERTY ('TestLogin', 'PasswordLastSetTime');
GO

-- Show all logins where the password is over 90 days old
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -90, GETDATE())
AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');
GO