Who changed a LOGIN (or its Password) and when

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:

WhoChangedLogin

 

 

 

Inspired by:

SQL SERVER – Who Changed the Password of SQL Login? – Interview Question of the Week #065

 

Leave a Reply

Your email address will not be published. Required fields are marked *