f T-SQL 檢核SQL Server Login 密碼到期日 ~ 迪貝之家

T-SQL 檢核SQL Server Login 密碼到期日

DECLARE password_expire_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT  name,cast(LOGINPROPERTY(name, 'DaysUntilExpiration')  as int) expired_day
FROM    sys.sql_logins
WHERE is_policy_checked = 1
and   cast(LOGINPROPERTY(name, 'DaysUntilExpiration')  as int) > 0
and    cast(LOGINPROPERTY(name, 'DaysUntilExpiration')  as int) < 7
and   LOGINPROPERTY(name, 'PasswordLastSetTime') > '1900-01-01 08:00:00.000' ;
DECLARE @name varchar(100);
DECLARE @expired_day int;
DECLARE @logic_disk char;
DECLARE @outputinfo varchar(MAX);
set   @outputinfo = N'N';

-- Open the cursor
OPEN password_expire_cursor;
-- Retrieve one row at a time from the cursor
FETCH NEXT
FROM password_expire_cursor
INTO @name,@expired_day ;

WHILE @@FETCH_STATUS = 0
begin

set   @outputinfo =  @outputinfo + ':' + @name + '/'+ cast(@expired_day as char) ;

   FETCH NEXT
   FROM password_expire_cursor
   INTO @name,@expired_day;
end

   -- Close the cursor
CLOSE password_expire_cursor;
-- Deallocate the cursor
DEALLOCATE password_expire_cursor;

if len(@outputinfo) = 1
   print N'Y';
else
   print  @outputinfo;