Skip to content

Create Daily Preemptive Alert for Login Expiry in SQL Server In 5 Easy Steps

by on September 12, 2014

1) Enable and Set Up Database Mail

Follow instructions here: http://technet.microsoft.com/en-us/library/ms191207(v=SQL.105).aspx

2) Create an Operator to recieve the Notificaitons & Alerts

SQL Server Agent > Operators > right Click > new Operator

3) Create a New error in master.dbo.sysmessages

Error: 60001   

Severity: 10   

Dlevel:128   

Description: Password is about to expire (or expired) for the user(s): %s.         Please, contact DBA and/or user(s) to reset the password(s).

Msglangid: 1033

NOTE: 1033 is English

4) Create a Maintenance Plan with Sql Query

  • Runs daily at 9:00 am
  • alerts on failure to run step in Plan (optional)
  • alerts on failure to run Job for plan (optional)

IF EXISTS (SELECT name, LOGINPROPERTY(name, N’DaysUntilExpiration’) as daysTillExpiry 
  FROM sys.server_principals
  WHERE type = ‘S’
    AND LOGINPROPERTY(name, N’DaysUntilExpiration’) < 6
    –AND default_database_name = ‘APP1_Prod’
    –AND name NOT IN (‘sa’, ‘app_service_login’)
    –AND is_disabled = 0
    –AND LOGINPROPERTY(name, N’IsLocked’) = 0
    )
BEGIN
DECLARE @str VARCHAR(400)
SELECT @str = COALESCE(@str + ‘,’, ”) + name + ‘[‘+convert(char(1),LOGINPROPERTY(name, N’DaysUntilExpiration’))+ ‘]’ FROM sys.server_principals
WHERE type = ‘S’ AND LOGINPROPERTY(name, N’DaysUntilExpiration’) < 6
  –AND default_database_name = ‘APP1_Prod’
RAISERROR (60001, 10, 1, @str  );
END

image

5) Create an Alert

Type: SQL Server Event Alert

Error Number: 60001 (or number used when creating alert – the Error field)

image

You should now be able to test this and receive an email alert!

If this does not work properly, try restarting the SQL Instance (no need to restart machine) to pick up any database mail settings/changes.

Advertisements

From → Microsoft, SQL Server

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: