— Show all logins where the password is over 60 days old
–查看60天没改密码的login
SELECT name, LOGINPROPERTY([name], ‘PasswordLastSetTime’) AS ‘PasswordChanged’
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], ‘PasswordLastSetTime’) < DATEADD(dd, -60, GETDATE());
From <https://www.mssqltips.com/sqlservertip/2379/auditing-sql-server-password-age/>
查询login什么时候过期
SELECT LOGINPROPERTY(name, 'BadPasswordCount') AS 'BadPasswordCount' name,
,LOGINPROPERTY(name, 'BadPasswordTime') AS 'BadPasswordTime'
,LOGINPROPERTY(name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
,LOGINPROPERTY(name, 'DefaultDatabase') AS 'DefaultDatabase'
,LOGINPROPERTY(name, 'DefaultLanguage') AS 'DefaultLanguage'
,LOGINPROPERTY(name, 'HistoryLength') AS 'HistoryLength'
,LOGINPROPERTY(name, 'IsExpired') AS 'IsExpired'
,LOGINPROPERTY(name, 'IsLocked') AS 'IsLocked'
,LOGINPROPERTY(name, 'IsMustChange') AS 'IsMustChange'
,LOGINPROPERTY(name, 'LockoutTime') AS 'LockoutTime'
,LOGINPROPERTY(name, 'PasswordHash') AS 'PasswordHash'
,LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
,LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS 'PasswordHashAlgorithm'
,is_expiration_checked As 'is_expiration_checked'
FROM sys.sql_logins
WHERE is_policy_checked = 1
window sid:
net user username /domain
关于Password Policy详细解释
