Alert particular set of users about PASSWORD EXPIRY policy notification
As you may be aware using SQL 2005 version you can set PASSWORD EXPIRY policy that can apply the same complexity and expiration policies used in Windows Server 2003 operating system to passwords used inside SQL Server. This functionality depends on the NetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions. Also you must set or follow the password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords.
As of now if you haven't enforced then this policy can be configured separately for each SQL Server login by using ALTER LOGIN statement to configure the password policy options of a SQL Server login. Fyi BOL content in using this policy too:
A known issue in Windows Server 2003 might prevent the bad password count from being reset after LockoutThreshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.
Then coming to alerting a group of users or set of department where they often forgot to change their passwords, you may need to automate the process and get an e-mail notification as soon as the password expires.
To accomplish this either you may need to take help of third party tools or native SQL methods by writing set of TSQL statements as a scheduled job. Again re-visiting the operating system policies (security policies on local server & domain) on the password expiry policy say for Windows 2003 its 25 days or whatever your security team set within the local server security policies including the domain policies. So why not they set such a documentation to change the password periodically whenever the time comes.
Use this TSQL to get list of login names and email using database mail to a corresponding contact:
SELECT datediff(day, updatedate, getdate()), updatedate, [name], dbname
FROM sys.syslogins
WHERE dbName = 'people'
