I'm new to SQL and have no training. Just trying to learn this on my
own.
I've got a system that grabs new users from 1 table and inserts them
into a user table for a diff. system. I need to be able to assign them
a random password. I've got code that will do this using NEWID() and
RAND() functions and works fine in Query analyser. But if I try to
write a function, I find I can't have NEWID() and RAND() in a UDF. I
can write this as a Procedure, but can't figure out complete syntax of
Proc. or how to call it in my SQL update command.
SQL update command is:
UPDATE clientaccess SET password= dbo.makepassword()
WHERE password = ''
of course, that's calling it as a function. How do I call a Proc in
same UPDATE code?
Here's code as function. How would I change to proc and call?
CREATE function dbo.GenerateRandomString ()
RETURNS char(8)
As
Begin
declare @.password char(8)
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = ''
while @.count <= 8
begin
set @.string = @.string +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
RETURN(@.password)
endTry putting the UPDATE in the proc definition and passing the account and
password as parameters. Note however that the end-user should change the
password (sp_password) ASAP as this approach is not very secure. Also, just
as an added "slight" layer of security , I'd recommend you use WITH
ENCRYPTION for the proc.
HTH
Jerry
<jfeldbruegge@.yahoo.com> wrote in message
news:1129045975.003145.224220@.g44g2000cwa.googlegroups.com...
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>|||Hi
NewID is a nondeterministic function and can't be used in a function, RAND
seems to cause issues even though it has a seed. To overcome this problems
you could pass a NEWID and Random number to the function, but that would be
less secure than using a different one for each character.
One solution when using a procedure is to loop or cursor for each row to
update:
CREATE PROCEdURE dbo.GenerateRandomString (@.password VARCHAR(8) OUTPUT)
AS
BEGIN
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = NULL
while @.count <= 8
begin
set @.password = ISNULL(@.password,'') +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
END
-- Example table
CREATE TABLE clientaccess ( username varchar(40) NOT NULL, password char(8)
NOT NULL DEFAULT ('') )
-- Example data
INSERT INTO clientaccess ( username ) values ( 'john' )
INSERT INTO clientaccess ( username ) values ( 'jim' )
INSERT INTO clientaccess ( username ) values ( 'jack' )
SELECT * FROM clientaccess
DECLARE @.password varchar(8), @.username varchar(40)
DECLARE user_cursor CURSOR FOR
SELECT username
FROM clientaccess
WHERE ISNULL(password,'') = ''
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.password = ''
EXEC dbo.GenerateRandomString @.password OUTPUT
UPDATE clientaccess
SET password = @.password
WHERE CURRENT OF user_cursor
FETCH NEXT FROM user_cursor INTO @.username
END
CLOSE user_cursor
DEALLOCATE user_cursor
GO
SELECT * FROM clientaccess
John
"jfeldbruegge@.yahoo.com" wrote:
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>
|||
Rishi Khetan wrote:
. And even I'm not a graduate, I'm still an undergrad. 
Rishi Khetan wrote: