Script to Find SQL Server Windows Login Expiration Date

By: Eli Leiba   |   Comments   |   Related: More > Database Administration

Problem

The requirement is to create a T-SQL script to report when a SQL Server Windows login password will expire.

Solution

The solution involves creating a T-SQL stored procedure in the SQL Server master database, called usp_GetLoginExpirationDate that will take a Windows login as input and will output the password expiration date for that login. If the Windows login is not found in the domain's active directory, nothing will be displayed.

Note: In order to be able to get the Windows login information, xp_cmdshell option should be enabled.

The procedure executes the command 'net user '  + login + ' /domain' to return the information needed to extract the Password Expires information.  By using the net user command, administrators can manage user accounts from a Windows command prompt. 

Here is sample output of the command for a specific login (the ... will be replaced with the actual values).

The procedure stores the output of the net user shell command in a table variable (@tempResLines) and then uses SQL to SELECT from that table the data related to the password expiration date and time. Note that other useful date can be also extracted in the same manner like Password Last Set date, Home directory, Last Logon, Logon hours allowed and local or global group memberships.

Here is the T-SQL code for the stored procedure:

USE master
GO

-- =================================================================================
-- Author:      Eli Leiba
-- Create date: 08-2019
-- Name:        dbo.usp_GetLoginExpirationDate
-- Description: This procedure reports windows login expiration date using TSQL 
-- =================================================================================

CREATE PROCEDURE dbo.usp_GetLoginExpirationDate (
	@userLogin NVARCHAR (30),
	@expirationDate NVARCHAR (30) OUTPUT
	)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @tempResLines TABLE (line NVARCHAR (80))
	DECLARE @cmd NVARCHAR (80)

	SET @cmd = CONCAT ( 'Net user ', @userLogin, ' /domain' )

	INSERT @tempResLines
	EXEC xp_cmdSHELL @cmd

	SELECT @expirationDate = RTRIM (LTRIM (RIGHT (LINE, LEN (LINE) - 16)))
	FROM @tempResLines
	WHERE line LIKE 'Password expires%'

	SET NOCOUNT OFF
END
GO

Here is an example for using the procedure:

Report the expiration date of the Windows login password for login "uo80r".

DECLARE @userLogin      nvarchar (30) = 'uo80r'
DECLARE @expirationDate nvarchar (30) = ''

EXEC dbo.usp_GetLoginExpirationDate @userLogin, @expirationDate OUTPUT

PRINT @expirationDate

And the results are (on my server):

When Not Running in a Domain

If you are not running in a domain and using a local Workgroup you could modify the above as follows to get the information.  The only change was to remove, ' /domain' from the SET @cmd line.

USE master
GO

-- =================================================================================
-- Author:      Eli Leiba
-- Create date: 08-2019
-- Name:        dbo.usp_GetLoginExpirationDate
-- Description: This procedure reports windows login expiration date using TSQL 
-- =================================================================================

CREATE PROCEDURE dbo.usp_GetLoginExpirationDate (
	@userLogin NVARCHAR (30),
	@expirationDate NVARCHAR (30) OUTPUT
	)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @tempResLines TABLE (line NVARCHAR (80))
	DECLARE @cmd NVARCHAR (80)

	SET @cmd = CONCAT ( 'Net user ', @userLogin )

	INSERT @tempResLines
	EXEC xp_cmdSHELL @cmd

	SELECT @expirationDate = RTRIM (LTRIM (RIGHT (LINE, LEN (LINE) - 16)))
	FROM @tempResLines
	WHERE line LIKE 'Password expires%'

	SET NOCOUNT OFF
END
GO

Next Steps

• You can create and compile this simple procedure in your master database and use it as a T-SQL tool for information about when a Windows login password will expire.
• The procedure was tested on Microsoft SQL Server 2014 and 2017, but should work will all versions and editions.
• Check out all of the SQL Server Security Tips.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips