INF: Implementing Password Expiration of SQL Server Login IDs

Last reviewed: April 3, 1997
Article ID: Q80397

The information in this article applies to:

  - 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
    

At this point, the system should be ready. Again, this can be varied greatly to meet your own needs.

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


Additional query words: 4.20 Windows NT
Keywords : kbusage SsrvAdmin SSrvGen
Version : 4.2
Platform : OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.