密码过期

DECLARE @login nvarchar(30)
 
-- 查詢設定密碼逾期的登入帳號
SELECT @login = name
FROM sys.sql_logins
WHERE (type = 's') AND (is_expiration_checked = 1)
 
-- 計算登入帳號密碼已使用天數
SELECT @login AS 'login',
    DATEDIFF(d, CAST(LOGINPROPERTY(@login, 'PasswordLastSetTime') AS datetime), GETDATE()) AS 'pwd_using_days'
 

-- 傳回密碼到期之前的剩餘天數
-- CAUTION : For SQL Server 2008 ONLY!
SELECT LOGINPROPERTY(@login, 'DaysUntilExpiration') AS 'days_until_expiration'