Recently at work we had an issue where a SQL login account’s password expired and the application that uses that login stopped working. The Window’s password policy for our organization requires passwords to expire after 60 days.
Management requested that we get “an email when the password is about to expire so that we can change it to prevent the application from not being able to connect.”
So I created a little script that runs daily (via a SQL Job) to check all SQL Server login accounts for passwords that will expire in ’10’ (you can change the number to whatever you want) days or less. If any results are found, it will generate and send an email with the SQL login as a reminder that you have “X” number of days until the password expires.
Below is the script. Feel free to use/modify it for your specific needs.
USE master; Go DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max) DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max) DECLARE @SQLAccountUser as varchar(25) DECLARE @DaysToExpire as varchar(10) = '10' -- you can pass this number as a parameter when calling this SP. --Declare Cursor and get all SQL Accounts that have 'Password Expiration' enabled. DECLARE get_SQLAccount CURSOR FOR select name from sys.sql_logins where is_disabled = 0 and is_expiration_checked = 1 and ( (SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) > 0 and (SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) <= @DaysToExpire ) --Open the cursor OPEN get_SQLAccount; FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser; WHILE @@FETCH_STATUS = 0 BEGIN SET @p_profile_name = N'Your Profile Name' SET @p_recipients = N'yourEmail@EmailProvider.com' SET @p_subject = N'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.' SET @p_body = 'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.' EXEC msdb.dbo.sp_send_dbmail @profile_name = @p_profile_name, @recipients = @p_recipients, @body = @p_body, @body_format = 'HTML', @subject = @p_subject FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser; END CLOSE get_SQLAccount; DEALLOCATE get_SQLAccount;