Warning Before SQL Login Passwords Expire With DaysUntilExpiration

DaysUntilExpiration tells you how many days a SQL login password has left. Read it along with the locked and disabled flags, and you can warn people before an application loses its connection. A warning that nobody owns is just noise.

The password that expired on a Friday

Picture a Friday night page. The billing application cannot connect. The error says the login failed. Nothing changed in the code. But the service account has a SQL login, and its password reached its expiry date.

The fix takes five minutes once you know. Finding out takes two hours. A small daily query can tell you weeks ahead. Let me build one with a demo login.

Create a login that expires

The demo creates a server-level object, a login named DemoLogin, and removes it at the end. It uses CHECK_POLICY and CHECK_EXPIRATION, so SQL Server asks Windows for the password rules.

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DemoLogin')
    DROP LOGIN DemoLogin;

CREATE LOGIN DemoLogin
    WITH PASSWORD = N'Demo#Only_9aXk!2',
         CHECK_POLICY = ON, CHECK_EXPIRATION = ON, DEFAULT_DATABASE = master;

Read the login properties together

One function does the work: LOGINPROPERTY. The query below reads the days left, the expired flag, the locked flag and the date the password was last set. I convert each to a plain type, so the report columns stay stable. Windows logins are not listed, because Windows manages their passwords.

SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
       CONVERT(int, LOGINPROPERTY(name, 'DaysUntilExpiration')) AS DaysUntilExpiration,
       CONVERT(int, LOGINPROPERTY(name, 'IsExpired'))           AS IsExpired,
       CONVERT(int, LOGINPROPERTY(name, 'IsLocked'))            AS IsLocked,
       CONVERT(datetime, LOGINPROPERTY(name, 'PasswordLastSetTime')) AS PasswordLastSetTime
FROM sys.sql_logins
ORDER BY name;

On my test server DemoLogin shows 42 days. That number comes from the password policy of the Windows machine, not from SQL Server, so yours may differ. Most other logins show NULL, because expiration is not checked for them. NULL means there is nothing to count down. It does not mean “safe,” so I never turn it into zero.

Build the warning list with an owner

Now make it useful. The list keeps only logins that check expiration and are not disabled. It includes a login that is within the warning window, or already expired. The window is an input, not a rule. I use 60 days here, only because my machine gives 42. Raise it until DemoLogin appears on yours.

The last column is the real point. A small table maps each login to the team that owns the application. A login with no owner shows NO OWNER, and that is your first job.

DROP TABLE IF EXISTS #LoginOwner;
CREATE TABLE #LoginOwner (LoginName sysname PRIMARY KEY, OwnerName nvarchar(60));
INSERT #LoginOwner VALUES (N'DemoLogin', N'Billing application team');

DECLARE @warningDays int = 60;

SELECT l.name,
       CONVERT(int, LOGINPROPERTY(l.name, 'DaysUntilExpiration')) AS DaysRemaining,
       CONVERT(int, LOGINPROPERTY(l.name, 'IsExpired'))           AS ExpiredFlag,
       CONVERT(int, LOGINPROPERTY(l.name, 'IsLocked'))            AS LockedFlag,
       ISNULL(o.OwnerName, N'NO OWNER')                           AS OwnerName
FROM sys.sql_logins AS l
LEFT JOIN #LoginOwner AS o ON o.LoginName = l.name
WHERE l.is_expiration_checked = 1 AND l.is_disabled = 0
  AND (CONVERT(int, LOGINPROPERTY(l.name, 'DaysUntilExpiration')) <= @warningDays
       OR CONVERT(int, LOGINPROPERTY(l.name, 'IsExpired')) = 1)
ORDER BY DaysRemaining, l.name;

DemoLogin appears with 42 days remaining, no expired flag, no lock, and its owner. Other logins on your server may appear too, and some may say NO OWNER.

Let silence mean something

Disabled logins are left out on purpose. Disable the demo login and run the same list again. It comes back empty.

ALTER LOGIN DemoLogin DISABLE;

DECLARE @warningDays int = 60;

SELECT l.name, CONVERT(int, LOGINPROPERTY(l.name, 'DaysUntilExpiration')) AS DaysRemaining
FROM sys.sql_logins AS l
WHERE l.name = N'DemoLogin' AND l.is_expiration_checked = 1 AND l.is_disabled = 0
  AND CONVERT(int, LOGINPROPERTY(l.name, 'DaysUntilExpiration')) <= @warningDays;

An empty list can mean nothing is near expiry. It can also mean the report failed to run, or you lack permission to see the logins. So record when the report last ran, and test the email path before you need it. Never put passwords, hashes or connection strings in the report. After a rotation, test every application that uses the login. Then clean up.

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DemoLogin')
    DROP LOGIN DemoLogin;
DROP TABLE IF EXISTS #LoginOwner;

Keep the report short enough that someone will read it.

An expiry warning is not a password policy, it is a task that needs an owner.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.