password trouble - 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)

password trouble

Author

Topic

moramoga
Starting Member

34 Posts

Posted - 2006-12-05 : 15:38:50

I have to make a Store procedure that change the password of a user, it receives the new password as a parameter, thats easy, but I have to validate that the password at least contains 5 letters from the alphabet and at least 2 numbers....does Sql has any functions for that o do I have to make them myself??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:49:10

[code]declare @password varchar(100)

select @password = '9pesow6'

if @password like '%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%' and @password like '%[0-9]%[0-9]%'
select 'password ok!'
else
select 'password not ok'[/code]
Peter Larsson
Helsingborg, Sweden

EDIT: Added A-Z for different collations.

moramoga
Starting Member

34 Posts

Posted - 2006-12-05 : 15:54:34

Thanks!

moramoga
Starting Member

34 Posts

Posted - 2006-12-05 : 17:01:30

I forgot it also has to check that includes a character thats it is not in the alphabet...how do I do it??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:06:05

Don't start a new thred for this.

declare @password varchar(100)

select @password = '9pesow6'

if @password like '%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%' and @password like '%[0-9]%[0-9]%' and @password like '%[^a-zA-Z0-9]%'
	select 'password ok!'
else
	select 'password not ok'

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:14:35

[code]CREATE FUNCTION dbo.fnIsValidPassword
(
@Password VARCHAR(8000)
)
RETURNS BIT
AS

BEGIN
DECLARE @Valid BIT

IF @Password LIKE '%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%[a-zA-Z]%' AND @Password LIKE '%[0-9]%[0-9]%' AND @Password LIKE '%[^a-zA-Z0-9]%'
SELECT @Valid = 1
ELSE
SELECT @Valid = 0

RETURN @Valid
END[/code]Call with

SELECT dbo.fnIsValidPassword('6pesog%4'), dbo.fnIsValidPassword('6peso%4')

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources