Modifying sp_password

Question

Post reply

Modifying sp_password

rocky

SSCrazy

Points: 2120
More actions
November 14, 2006 at 9:17 am

#62673

I found this script for modifying sp_password.
My requirements are that the password must be at least 8 characters long and have a
minimum of 1 numeric character in the 2nd through next-to-last position.
I've got the 8 char long below. Can someone tell me how to do the minimum of 1 numeric character in the 2nd through next-to-last position?
thanks!
declare @position smallint,                                              --
   @numCaps smallint,                                              --
   @numDigit smallint,                                              --
   @charEqual smallint,                                              --
   @charLast varchar(1),                                            --
   @charError varchar(255)                                           --
                                                                               --
    select @charError = 'Password requirements: Minimum 8 characters long, minimum 1 numeric character in the 2nd through next-to-last position.'
                                                                               --
select @position = 1,                                                     --
   @numCaps = 0,                                                      --
   @numDigit = 0,                                                     --
   @charEqual = 1,                                                    --
   @charLast = ''                                                     --
                                                                               --
-- Password equal loginname                                                --
if ( lower(@new) = lower(@loginame) )                                      --
begin                                                                      --
  raiserror (@charError, 16, 1)                                          --
  return (1)                                                             --
end                                                                        --
                                                                               --
-- Password long enough                                                    --
if ( datalength(convert(varchar, @new)) < 8 OR @new IS NULL )              --
begin                                                                      --
  raiserror (@charError, 16, 1)                                          --
  return (1)                                                             --
end                                                                        --
                                                                               --
-- Check syntax of password                                                --
while( @position <= datalength(convert(varchar, @new)))                    --
begin                                                                      --
  ????????????????
end

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How about this :

DECLARE @pass TABLE (pwd CHAR(8) PRIMARY KEY CLUSTERED)

INSERT INTO @pass (pwd)

SELECT '12345678' --valid??

UNION ALL

SELECT 'abcdefgh' --not valid

UNION ALL

SELECT 'abcdefg1' --not valid

UNION ALL

SELECT '1bcdefgh' --not valid

UNION ALL

SELECT 'a2cdefgh' --valid

UNION ALL

SELECT 'abcdef7h' --valid

SELECT Pwd, CASE WHEN pwd LIKE '_%[0-9]%_' THEN 'Valid' ELSE 'NOT VALID' END AS PwdCheck FROM @pass ORDER BY 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

One step better... all validation with check constraints (just add the case statement to another check constraint / trigger (better error messages) and you're golden) :

CREATE TABLE #Pass (Userid int identity (1,1) primary key NONCLUSTERED, pwd CHAR(8) COLLATE Latin1_General_CS_AS CONSTRAINT c_#pass_Len8 CHECK (LEN(Pwd) = 8))

INSERT INTO #Pass (pwd)

SELECT '12345678' --valid??

UNION ALL

SELECT 'abcdefgh' --not valid

UNION ALL

SELECT 'abcdefg1' --not valid

UNION ALL

SELECT '1bcdefgh' --not valid

UNION ALL

SELECT 'a2cdefgh' --valid

UNION ALL

SELECT 'abcdef7h' --valid

SELECT Pwd, CASE WHEN pwd LIKE '_%[0-9]%_' THEN 'Valid' ELSE 'NOT VALID' END AS PwdCheck FROM #Pass ORDER BY 2

INSERT INTO #Pass (pwd) values ('1234567 ')--invalid length

INSERT INTO #Pass (pwd) values ('12345')--invalid length

Select * from #Pass where pwd = UPPER('abcdef7h')

--password not found because of collation setting

DROP TABLE #Pass

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 3

This should go without saying, but modifying sp_password will bring your server into a potentially unsupported state. Otherwise, piggy backing on the previous two posts by Remi, testing that specific condition can be done by:

DECLARE @DigitTest tinyint

SET @DigitTest = (SELECT CASE WHEN @new LIKE '_%[0-9]%_' THEN 1 ELSE 0 END )

Obviously, for @DigitTest 0 = False and 1 = True.

K. Brian Kelley
@kbriankelley

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Thanx for catching this. I didn't know that system procedure .

Ya much better off doing this with DRI / TRIGGER than using a ~~[system]~~ ANY procedure. Also don't use the sp_ prefixe on your procedure. The server goes to master to find the proc and then back to the current database when it doesn't... You'll lose performance over time.

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 5

Unfortunately, SQL Server 2000 doesn't have the ability to enforce password complexity. SQL Server 2005 will enforce what the OS knows if you are running on Windows Server 2003 (Windows 2000 doesn't have the right API calls). Any time you modify a system stored procedure, Microsoft reserves the right not to support you.

There are usually two ways to meet the control. One is by catching it at the time of password change. Another is by auditing the passwords and then following up on those that don't meet the complexity requirements. Is the second option available to you? If so, then you can look at a tool like Cain & Abel.

Cain & Abel Download

K. Brian Kelley
@kbriankelley