Technical Article

SQL Login Password Audit

,

This stored procedure will find all the SQL Logins on a server and tell you the age of the logins password and it will also check for blank passwords and passwords that are the same as the login name.  If you create a common password table, it will also check the logins passwords against those in your table.  The common password table format is:

CREATE TABLE [dbo].[CommonPwds](
[pwd] [varchar](25) NULL
) ON [PRIMARY]
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;

Rate

5 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (3)

You rated this post out of 5. Change rating