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;