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;