Database User – Password Expire Plocy Notification | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database User – Password Expire Plocy Notification

Hi,
I have created a database user with password expire policy. I want to automate the process and get an e-mail notification as soon as the password expires.
I believe you may need to take help of third party tools in this case, haven’t seen so far anyone implemented using native SQL methods. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Try to create an alert based on the error number… 18487
Login failed for user ‘%.*ls’. Reason: The password of the account has expired. I have not tested this but you can test it… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

The goal is to notify the application team via an e-mail so that they can change the configuration on the application
The password expiry policy will go with operating system, 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. If not you have to write a program to identify in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I have the following solution. I can get the user info form the sys.syslogins table. Since I know the password expiry policy (no. days). I can send an email using database mail.
something like this SELECT datediff(day, updatedate, getdate()), updatedate, [name], dbname
FROM sys.syslogins
WHERE dbName = ‘people’ if the date is = 90 days then
send mail – dataase mail. I can schedule the above stored procedure.
]]>