sp_password
-
I found this script for altering master.sp_password. What I want to know is what does the code in bold do? Is it looking for just capitals or also special characters? I'm having a difficult time finding out the ascii values for special chars, numerics, caps, alphas.
thanks!
USE master
go
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER procedure sp_password
@old sysname = NULL, -- the old (current) password
@new sysname, -- the new password
@loginame sysname = NULL -- user to change password on
as
---------------------------------------------------------------------------------
-- Modified script to meet xxx Password Standard for passwords --
-- Created by: xxx --
-- Created when: xxxxxxxx --
---------------------------------------------------------------------------------
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @self int
select @self = CASE WHEN @loginame is null THEN 1 ELSE 2 END
-- RESOLVE LOGIN NAME
if @loginame is null
select @loginame = suser_sname()
-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @self = 1
begin
dbcc auditevent (107, @self, 0, @loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
return (1)
end
ELSE
begin
dbcc auditevent (107, @self, 1, @loginame, NULL, NULL, NULL)
end
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
return (1)
end
-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @loginame and isntname = 0
AND sysadmin = 1) )
SELECT @self = 1
-- CHECK OLD PASSWORD IF NEEDED --
if (@self = 1 or @old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @loginame and
( (@old is null and password is null) or
(pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
return (1)
end
---------------------------------------------------------------------------------
-- Modified script to meet xxx standard for passwords --
-- Created by: xxx --
-- Created when: xxxxxxxx --
---------------------------------------------------------------------------------
--
declare @position smallint, --
@numCaps smallint, --
@numDigit smallint, --
@charEqual smallint, --
@charLast varchar(1), --
@charError varchar(255) --
--
select @charError = 'Password requirements: Minimum 10 characters long, minimum 2 alphanumeric characters, minimum 1 uppercase character, max 2 equal characters after each other and can not be same as login.'
--
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)) < 10 OR @new IS NULL ) --
begin --
raiserror (@charError, 16, 1) --
return (1) --
end --
--
-- Check syntax of password --
while( @position <= datalength(convert(varchar, @new))) --
begin --
-- A-Z, Å, Ä, Ö, --
if ( --
ascii(substring(@new,@position, 1)) between 65 and 90 or -- A-Z --
ascii(substring(@new,@position, 1)) between 196 and 197 or -- Å
ascii(substring(@new,@position, 1)) = 214 or -- Ö
ascii(substring(@new,@position, 1)) = 198 or -- Æ
ascii(substring(@new,@position, 1)) = 216 -- Ø
 
--begin --
select @numCaps = @numCaps + 1 --
end --
--
-- 0-9 --
if ( --
ascii(substring(@new,@position, 1)) between 48 and 57 -- 0-9
 
--begin --
select @numDigit = @numDigit + 1 --
end --
--
-- More then two equal characters after each other --
if ( @charLast = substring(@new,@position, 1) ) --
begin --
select @charEqual = @charEqual + 1 --
if ( @charEqual >= 3 ) --
begin --
raiserror (@charError, 16, 1) --
return (1) --
end --
end --
else --
begin --
select @charEqual = 1 --
end --
--
select @charLast = substring(@new,@position, 1) --
select @position = @position + 1 --
--
end --
--
if ( @numCaps < 1 ) --
begin --
raiserror (@charError, 16, 1) --
return (1) --
end --
--
if ( @numDigit < 2 ) --
begin --
raiserror (@charError, 16, 1) --
return(1) --
end --
--
---------------------------------------------------------------------------------
-- CHANGE THE PASSWORD --
update master.dbo.sysxlogins
set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
where name = @loginame and srvid IS NULL
-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
exec('use master grant all to null')
-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @@error <> 0
return (1)
raiserror(15478,-1,-1)
return (0) -- sp_password
GO
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
-
Working from the inner most () out it does...
substring(pos, pos) picks a character from a string
ascII() get's the ASCII code for the character it chose from the string...
the IF() compares it to other numeric values. and it it compares there begins the next statement...
what exactly are your trying to do, perhaps we can help...
-
It's looking for capital letters and/or those specified 'special' characters.
-SQLBill
-
Is this line the only one looking for caps?
ascii(substring(@new,@position, 1)) between 65 and 90 or -- A-Z --
Are these lines looking only for special chars?
ascii(substring(@new,@position, 1)) between 196 and 197 or -- Å
ascii(substring(@new,@position, 1)) = 214 or -- Ö
ascii(substring(@new,@position, 1)) = 198 or -- Æ
ascii(substring(@new,@position, 1)) = 216 -- Ø
I just want to differentiate which is which.
thanks for the replies!
-
All of them are. They are handling capital letters in languages other than English.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply