USE [master] GO /* Test Script: CREATE LOGIN [SQLLogin1] WITH PASSWORD=N'@Very$trongP@ssw0rd123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [master] GO ALTER LOGIN [SQLLogin1] WITH PASSWORD=N'VerystrongP@ssword123' GO */ WITH alteredLogins AS ( SELECT l.[Transaction SID] ,suser_sname(l.[Transaction SID]) AS ChangedByUserName, l.[Begin Time], l2.[Lock Information], SUBSTRING(l2.[Lock Information], CHARINDEX('SERVER_PRINCIPAL(principal_id = ', l2.[Lock Information]) + 32, 15) as PrincipalIdString FROM ::fn_dblog(DEFAULT, DEFAULT) l INNER JOIN ::fn_dblog(DEFAULT, DEFAULT) l2 ON l.[Transaction ID] = l2.[Transaction ID] AND l2.[Lock Information] LIKE '%SERVER_PRINCIPAL%' WHERE l.[Transaction Name] = 'ALTER LOGIN' ), preResult AS ( SELECT (SELECT name FROM sys.server_principals p WITH (READUNCOMMITTED) WHERE p.principal_id = CAST(SUBSTRING(PrincipalIdString, 1, CHARINDEX(')', PrincipalIdString) - 1) as int) ) as Changed_Login_Name, CAST(SUBSTRING(PrincipalIdString, 1, CHARINDEX(')', PrincipalIdString) - 1) as int) as Changed_Login_Principal_id, ChangedByUserName, alteredLogins.[Begin Time], alteredLogins.[Transaction SID], alteredLogins.[Lock Information] FROM alteredLogins WITH (READUNCOMMITTED) ) SELECT * FROM preResult WITH (READUNCOMMITTED) WHERE Changed_Login_Name = 'SQLLogin1' /* Search for a specific Login Name for which you want to know who change it (or the password) */ OPTION (RECOMPILE)
This solution only works if the Transaction Log still contains the information of the last change. The previous script returns the following resulting rows:
Inspired by:
SQL SERVER – Who Changed the Password of SQL Login? – Interview Question of the Week #065