Script to Find SQL Server Windows Login Expiration Date

By:   |   Updated: 2019-10-07   |   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).

User Name
Full Name
Comment
User's comment
Country/region code
Account active (yes/no)
Account expires
Password last set
Password expires
Password changeable
Password required
User may change password (yes/no)
Workstations allowed
Logon script
User profile
Home directory
Last Logon
Logon hours allowed
Local group memberships
Global group memberships

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):

query output

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.


Last Updated: 2019-10-07


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools