Microsoft KB Archive/80397

= How to implement password expiration dates for SQL Server 2000 or SQL Server 7.0 login IDs =

Article ID: 80397

Article Last Modified on 1/10/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q80397



Introduction
This article discusses a method that you can use to implement password expiration dates for Microsoft SQL Server logins. The method uses one user-created table, and three stored procedures. This method is just one variation that you can use, and you can modify the method to fit your own needs.



MORE INFORMATION
If you have Microsoft SQL Server running in Integrated Security mode under Microsoft Windows 2000 or a later version, you can rely on Windows for the expiration of the password. Whenever possible, we recommend that you use the Integrated Security mode for connections to SQL Server. Windows authentication helps provide security enforcement mechanisms such as stronger authentication protocols, and mandatory password complexity and expiration.

The method discussed in this article applies to an instance of SQL Server that is running in mixed security mode.

  Create a table in the master database that will hold the following:  The user login ID. The date the password was last changed. The last password that was used.

Note Only the system administrator (sa) should have access to this table.

CREATE TABLE PW_DATE (SID  VARBINARY(85),       DATE   DATETIME,       OLD_PW SYSNAME NULL)   Create a stored procedure that will determine whether the password has expired. If the password has expired, the procedure will then assign the login ID a new password, and the user must have the sa change it back. Additionally, you may also want to include other checks that you consider appropriate for your environment. The only two logins the procedure will not change the password for are the sa or the PROBE logins. The following example sets the password to expire after 30 days, and it lists all the users whose passwords have expired.

Note Only the sa should have EXECUTE permission.

CREATE PROCEDURE SP_PASSWORD_CHECK(@secret nvarchar(128)) AS   DECLARE @sid varbinary(85) DECLARE @date datetime DECLARE @name nvarchar(128) DECLARE crsPW CURSOR FOR SELECT SID,[DATE] FROM PW_DATE WHERE DATEADD(DAY, 30, PW_DATE.DATE) <= GETDATE OPEN crsPW FETCH NEXT FROM crsPW INTO @sid, @date WHILE (@@FETCH_STATUS=0) BEGIN SELECT @name = [NAME] FROM syslogins WHERE sid = @sid AND syslogins.[name] NOT IN ('sa', 'probe') IF @@ROWCOUNT = 1 EXEC sp_password NULL, @secret, @name FETCH NEXT FROM crsPW INTO @sid, @date END CLOSE crsPW DEALLOCATE crsPW   Run the SP_PASSWORD_CHECK procedure nightly by using a Transact-SQL script that is similar to the following:

USE MASTER GO   EXEC SP_PASSWORD_CHECK 'gulliver' GO To run the Transact-SQL script run nightly, add a Transact-SQL Script step to a New Job in SQL Server Agent, and then schedule the job to run nightly. </li>  Instead of using the SP_ADDLOGIN and the SP_PASSWORD stored procedures, you will use two new stored procedures. The SP_CORP_ADDLOGIN stored procedure replaces the SP_ADDLOGIN stored procedure, and the SP_CORP_PASSWORD stored procedure replaces the SP_PASSWORD stored procedure. To make sure that the old procedures are not used, revoke execute permissions for both the SP_ADDLOGIN stored procedure and for the SP_PASSWORD stored procedure.

Note You can modify the SP_ADDLOGIN and the SP_PASSWORD stored procedures. However, if you install an upgrade, make sure that you do not remove the SP_ADDLOGIN or the SP_PASSWORD stored procedures.

CREATE PROCEDURE SP_CORP_ADDLOGIN @LOGIN_ID VARCHAR(30), @PASSWD VARCHAR(30) = NULL, @DEFDB VARCHAR(30) = NULL AS     EXEC SP_ADDLOGIN @LOGIN_ID, @PASSWD, @DEFDB INSERT INTO PW_DATE (SID, DATE, OLD_PW) VALUES (SUSER_SID(@LOGIN_ID), GETDATE, NULL)

GO

CREATE PROCEDURE SP_CORP_PASSWORD @OLD VARCHAR(30) = NULL, @NEW VARCHAR(30), @LOGIN_ID VARCHAR(30) = NULL AS     EXEC SP_PASSWORD @OLD, @NEW, @LOGIN_ID IF (@LOGIN_ID = NULL) BEGIN UPDATE PW_DATE SET DATE = GETDATE, OLD_PW = @OLD WHERE SID = SUSER_SID END ELSE BEGIN UPDATE PW_DATE SET DATE = GETDATE, OLD_PW = @OLD WHERE SID = SUSER_SID(@LOGIN_ID) END

</li>  The final step is to initialize the system. If you have an active system with user login IDs, the sa should run the following query to load the PW_DATE table with the starting values:

INSERT INTO PW_DATE (SID, DATE, OLD_PW) SELECT SYSLOGINS.SID, GETDATE, SYSLOGINS.PASSWORD FROM SYSLOGINS </li></ol>

Now, the system should be ready. You can vary this method to meet your own needs.

Important The sa must not use Enterprise Manager, or use any other application that uses menu-driven methods for adding logins.

<div class="references_section">