INF: Implementing Password Expiration of SQL Server Login IDs

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

This article discusses one way to implement password expiration dates on SQL Server logins. This process uses one user-created table and three stored procedures. This is just one variation of the process and it can be modified for your own needs.

Note that if you have Microsoft SQL Server running in Integrated Security mode under NT you can rely on the expiration of the NT password. The methodology discussed here applies to Microsoft SQL Server running in mixed or standard security mode as well as to Microsoft SQL Server for OS/2.

1. Create a table in the master database that will hold the user login ID, the date the password was last changed and the last password used.

   NOTE: Only the system administrator (SA) should have access to this table.

         CREATE TABLE PW_DATE
         (SUID   SMALLINT,
          DATE   DATETIME,
          OLD_PW SYSNAME(30) NULL)
   
   

2. Create a stored procedure that will check and determine if the password has expired. If it has, the procedure will then assign the login ID a new password and the user must have the system administrator change it back. The only two logins the procedure will not change the password for are SA and PROBE; this is for system safety. The following example sets the password to expire after 30 days and also lists all users whose passwords have expired.

   NOTE: Only the SA should have EXECUTE permission.

         CREATE PROCEDURE SP_PASSWORD_CHECK
         @SECRET VARCHAR(30)
         AS
         UPDATE SYSLOGINS
           SET PASSWORD = @SECRET
           FROM PW_DATE
           WHERE SYSLOGINS.SUID = PW_DATE.SUID
             AND DATEADD(DAY, 30, PW_DATE.DATE) <= GETDATE()
             AND SYSLOGINS.NAME NOT IN ('sa', 'probe')
         SELECT NAME
           FROM SYSLOGINS
           WHERE PASSWORD = @SECRET
   
   

3. The SP_PASSWORD_CHECK procedure should then be run as a nightly batch process that resembles the following:

         USE MASTER
         GO
         SP_DBOPTION ALLOWUPDATES, 1
         RECONFIGURE WITH OVERRIDE
         GO
         SP_PASSWORD_CHECK gulliver
         GO
         SP_DBOPTION ALLOWUPDATES, 0
         RECONFIGURE WITH OVERRIDE
         GO
   

   To have such a batch process run nightly, the administrator can use either the LAN Manager AT command, the SQL WAITFOR command or the Windows NT AT command.

4. The last two stored procedures should then be used instead of the normal system stored procedures. SP_CORP_ADDLOGIN replaces SP_ADDLOGIN, and SP_CORP_PASSWORD should be used in place of SP_PASSWORD. To ensure that the old procedures are not used, execute permission should be revoked on SP_ADDLOGIN and SP_PASSWORD.

   NOTE: SP_ADDLOGIN and SP_PASSWORD can be modified, but care should be taken in case of upgrades to ensure that the changes are not removed.

         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 (SUID, DATE, OLD_PW)
           VALUES (SUSER_ID(@LOGIN_ID), GETDATE(), NULL)
   
   
         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 SUID = SUSER_ID ()
         END
         ELSE BEGIN
           UPDATE PW_DATE
           SET DATE = GETDATE(), OLD_PW = @OLD
           WHERE SUID = SUSER_ID(@LOGIN_ID)
   
   

5. The final step is to initialize the system. If you have an active system with user login IDs, the following query should be run by the SA to load the PW_DATE table with the necessary values:

         INSERT INTO PW_DATE (SUID, DATE, OLD_PW)
         SELECT SYSLOGINS.SUID, GETDATE(), SYSLOGINS.PASSWORD
         FROM SYSLOGINS
   

NOTE: It is important that the SA does not use SQL Administrator, or any other application that uses menu-driven methods for adding logins.