Situatie
Am creat o procedura ce este in special folositoare pentru rapoartele de audit. Procedura returneaza pe serverul sql pe care este rulata, pentru fiecare login in parte, politica de parole setata. Ea returneaza in plus si numarul de parole gresit introduse pt fiecare login, data ultimei introduceri gresite de parola, data cand a fost schimbata ultima data parola precum si data cand a fost contul blocat ultima oara.
Solutie
CREATE PROCEDURE BD_ListLoginsPasswordPolicy_prc
as
SELECT
name,
CASE CAST(is_policy_checked AS TINYINT) + CAST(is_expiration_checked AS TINYINT)
WHEN 0 THEN ‘Not Enforced’
WHEN 1 THEN ‘Password – No Expiration’
WHEN 2 THEN ‘Password With Expiration’
END AS PasswordEnforcement ,
LOGINPROPERTY(name,’BadPasswordCount’) AS BadPasswordCount,
LOGINPROPERTY(name,’BadPasswordTime’) AS BadPasswordTime,
LOGINPROPERTY(name,’DaysUntilExpiration’) AS DaysUntilExpiration,
default_database_name,
CASE WHEN LOGINPROPERTY(name,’IsExpired’) = 0 THEN ‘NO’ ELSE ‘YES’ END AS IsExpired,
CASE WHEN LOGINPROPERTY(name,’IsLocked’) = 0 THEN ‘NO’ ELSE ‘YES’ END AS IsLocked,
CASE WHEN LOGINPROPERTY(name,’IsMustChange’) = 0 THEN ‘NO’ ELSE ‘YES’ END AS IsMustChange,
LOGINPROPERTY(name,’LockoutTime’) AS LockoutTime,
LOGINPROPERTY(name,’PasswordLastSetTime’) AS PasswordLastSetTime
FROM sys.sql_logins
ORDER BY name
Leave A Comment?