modifying sp_password - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

modifying sp_password

Author

Topic

straw
Starting Member

3 Posts

Posted - 2006-11-14 : 14:34:17

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 14:40:37

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59194

Peter Larsson
Helsingborg, Sweden

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-11-14 : 14:52:37

declare @new varchar(25), @loginame varchar(25)
select @loginame = 'userid', @new='pa3sword1'

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
end --
--
-- Password long enough --
if ( datalength(convert(varchar, @new)) < 8 OR @new IS NULL ) --
begin --
raiserror (@charError, 16, 1) --
return
end --
--
-- Check syntax of password --
select @numDigit = 0, @position = 0
while( @position <= datalength(convert(varchar, @new))) --
begin --
set @charLast = substring(@new, @position, 1)
if @charLast in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
BEGIN
set @numDigit = @position
break
END
set @position = @position + 1
end
if @numDigit < 2
raiserror (@charError, 16, 1) --

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

straw
Starting Member

3 Posts

Posted - 2006-11-16 : 11:12:47

Thanks for the replies. How could I make it so that it cannot begin with a numeric and instead of a
minimum of 1 numeric character in the 2nd through next-to-last position it should be a minimum of nonalpha char (numeric or special char okay)?
thanks!

straw
Starting Member

3 Posts

Posted - 2006-11-16 : 11:14:41

I meant it cannot begin or end with a numeric.
thanks!

medwards
Starting Member

1 Post

Posted - 2006-12-20 : 23:26:22

The if statement is the real part of the code that does the validation.

Create Function dbo.IsInvalidPassword (@new sysname)
Returns bit
AS
BEGIN
Declare @RetVal Bit
set @new = ltrim(rtrim(@new))

-- Meat of the code
If Len(@new) >= 8 AND CharIndex(' ',@new) = 0 AND
@new Like '[a-z]%[0-999^9%9]9[9_$!"#&()*+,-./:;<=>?@{|}~'']%[a-z]' ESCAPE 9
BEGIN
Set @RetVal = 0 -- False, password is not invalid
END
ELSE
Set @Retval = 1 -- True, password is invalid

Return (@RetVal)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources