INF: Implementing Password Expiration of SQL Server Login IDsID: Q80397
|
CREATE TABLE PW_DATE
(SUID SMALLINT,
DATE DATETIME,
OLD_PW SYSNAME(30) NULL)
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
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.
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)
INSERT INTO PW_DATE (SUID, DATE, OLD_PW)
SELECT SYSLOGINS.SUID, GETDATE(), SYSLOGINS.PASSWORD
FROM SYSLOGINS
Additional query words: 4.20 Windows NT
Keywords : kbusage SSrvAdmin SSrvGen
Version : 4.2
Platform : OS/2 WINDOWS
Issue type :
Last Reviewed: March 12, 1999