Situatie
Pentru cazurile in care avem o asemenea cerinta de raport de la audit sau pur si simplu pentru simplificarea adiministrarii serverului de sql, am facut o procedura ce returneaza fiecare login de pe un server de sql pe care e rulata si, pentru fiecare, returneaza urmatoarele coloane:
ServerName – numele serverului
SQL_Login – numele login-ului
IsSysAdmin – daca este sau nu membru al rolului sysadmin de pe server
IsWeakPassword – daca are parola slaba , adica daca are doar litere sau daca este la fel ca login-ul, etc
WeakPassword – contine date doar daca coloana anterioara are valoarea 1, si in ea este explicatia pt valoarea 1
PwdLastUpdate – ultima oara cand a fost schimbata parola
DateAudited – data si ora rularii procedurii
Solutie
CREATE PROCEDURE SqlServerPwdCheck
AS
BEGIN
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;
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 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 TABLE #TempAudit;
UPDATE SQLPasswordAudit
SET PwdDaysOld = DATEDIFF(day,PwdLastUpdate,GETDATE());
UPDATE SQLPasswordAudit
SET IsWeakPassword = 0,
WeakPassword = ”;
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;
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 = ”;
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;
select * from [SQLPasswordAudit]
order by 5 desc, 8 desc
END;
Leave A Comment?