SQL Login Password Audit

CREATE PROCEDURE usp_SQLPasswordAudit
AS

BEGIN

	-- if table does not exist create it.
	IF OBJECT_ID(N'dbo.SQLPasswordAudit', N'U') IS NULL
	BEGIN

		PRINT 'Creating Table'

		CREATE TABLE SQLPasswordAudit(
			ID INT IDENTITY(1,1) NOT NULL,
			ServerName VARCHAR(50) NOT NULL,
			SQL_Login VARCHAR(50) NOT NULL,
			IsSysAdmin BIT NOT NULL DEFAULT(0),
			IsWeakPassword BIT NOT NULL DEFAULT(0),
			WeakPassword VARCHAR(250) NULL,
			PwdLastUpdate DATETIME2 NOT NULL,
			PwdDaysOld INT NULL,
			DateAudited DATETIME2 NOT NULL DEFAULT(GETDATE())
		);

		CREATE CLUSTERED INDEX [cluster_idx_ID] ON [dbo].[SQLPasswordAudit]
		([ID] ASC)
		WITH (FILLFACTOR = 90);

	END;




	-- Get all SQL Logins
	SELECT 
		@@ServerName ServerName, 
		a.name AS SQL_Login, 
		b.sysadmin AS IsSysAdmin,
		CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME) AS 'PwdLastUpdate'
	INTO #TempAudit
	FROM sys.sql_logins a
	LEFT JOIN MASTER..syslogins b ON a.sid = b.sid
	WHERE a.name NOT LIKE '##%';

	
	-- merge with perm table
	MERGE INTO SQLPasswordAudit a
	USING #TempAudit b ON a.SQL_Login = b.SQL_Login 

	WHEN MATCHED AND (a.PwdLastUpdate != b.PwdLastUpdate OR a.IsSysAdmin != b.IsSysAdmin) THEN
		UPDATE
		SET a.PwdLastUpdate = b.PwdLastUpdate,
		 a.IsSysAdmin = b.IsSysAdmin
	
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (ServerName, SQL_Login,IsSysAdmin, PwdLastUpdate)
		VALUES (b.ServerName, b.SQL_Login, b.IsSysAdmin, b.PwdLastUpdate)

	WHEN NOT MATCHED BY SOURCE THEN
		DELETE;

	-- drop temp table
	DROP TABLE #TempAudit;

	-- calculate the number of days old the passwords are
	UPDATE SQLPasswordAudit
	SET PwdDaysOld = DATEDIFF(day,PwdLastUpdate,GETDATE());

	-- reset fields for password 
	UPDATE SQLPasswordAudit
	SET IsWeakPassword = 0,
		WeakPassword = '';

	-- check if password is blank
	UPDATE SQLPasswordAudit
	SET WeakPassword = '[BLANK PASSWORD]', 
		IsWeakPassword = 1
	FROM SQLPasswordAudit a
	LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
	WHERE PWDCOMPARE('', b.password_hash) = 1;


	-- check if password is same as login
	UPDATE SQLPasswordAudit
	SET WeakPassword = 'Same As Login',
		IsWeakPassword = 1
	FROM SQLPasswordAudit a
	LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
	WHERE PWDCOMPARE(a.SQL_Login, b.password_hash) = 1 
	AND WeakPassword = '';

	-- check the common password table if it exists
	IF OBJECT_ID(N'dbo.CommonPwds', N'U') IS NOT NULL
	BEGIN

		UPDATE SQLPasswordAudit
		SET IsWeakPassword = 1,
			WeakPassword = 'WEAK - ' + c.pwd
		FROM SQLPasswordAudit a
		LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
		CROSS JOIN CommonPwds c
		WHERE PWDCOMPARE(c.pwd, password_hash) = 1 
		AND WeakPassword = '';

	END;

END;