sql server查看密码使用天数和剩余天数

将dbname改为想查询的用户名即可

DECLARE @login nvarchar(30)
 
-- 查询设定密码过期的登陆账号
SELECT @login = name
FROM sys.sql_logins
WHERE is_expiration_checked = 1
      and name = 'dbname'
  
 
-- 计算登录账户密码已使用天数
SELECT @login AS 'login', 
       DATEDIFF(d, 
                CAST(LOGINPROPERTY(@login, 
                                   'PasswordLastSetTime') AS datetime),
                GETDATE()) AS 'pwd_using_days'
--查询剩余天数
SELECT LOGINPROPERTY(@login, 'DaysUntilExpiration') AS 'days_until_expiration'