ID Number: Q80397
1.10 1.11
OS/2
Summary:
This article discusses one way to implement password expiration dates
on SQL Server logins. This process makes use of one user-created table
and three stored procedures. This is just one variation of the process
and can be modified for your own needs.
More Information:
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 or the SQL WAITFOR command.
4. The last two stored procedures should then be used instead of the
normal system stored procedures. SP_CORP_ADDLOGIN will replace
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 to go. Again, this can be
varied greatly to meet your own needs.
Note: It is important that the SA does not use the System
Administration Facility (SAF), or any other application that uses
menu-driven methods for adding logins.