SQL Server Email Alerts Setup

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;