Auditing SQL 2008

Auditing SQL Server Password Age

-- Show all logins where the password is over 60 days old
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
 FROM sys.sql_logins
 WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

-- Show all logins where the password is over 60 days old disregarding specific SQL Server Logins [##MS_PolicyTsqlExecutionLogin##, ##MS_PolicyEventProcessingLogin##]
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
 FROM sys.sql_logins
 WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
 AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');

-- Show all logins where the password was changed within the last day
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged' FROM sys.sql_logins WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());
O
R
-- Show all logins where the password is more than 1 month

Select loginname from master..syslogins where datediff(Month,updatedate,getdate()) > 1

reference site: http://www.mssqltips.com/sql_server_dba_tips.asp