How to implement password expiration dates for SQL Server 2000 or SQL Server 7.0 login IDs (80397)

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.

1. 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)

2. 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

3. 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.
4. 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
   
   				

5. 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

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.