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 2005 Forums
 Transact-SQL (2005)
 update column to random string (for assigned PW)

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 08:02:54
Hey,

I'm wondering if anybody has a function that will create a random string. Basically I want to update a column in my table with a random array of characters.

I don't need any high security mandatory letter and number combinations, but I'll take anything I can get.


much appreciated, thanks! :)

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 08:05:20
UPDATE Table1
SET Col1 = LEFT(REPLACE(CAST(NEWID() AS CHAR(40)), '-', ''), @Characters)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 08:07:08
For a five-digit password starting with 1
UPDATE	Table1
SET Col1 = 10000 + ABS(CHECKSUM(NEWID())) % 90000



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 08:07:53
quote:
Originally posted by Peso

UPDATE Table1
SET Col1 = LEFT(REPLACE(CAST(NEWID() AS CHAR(40)), '-', ''), @Characters)


E 12°55'05.63"
N 56°04'39.26"




Hey Peso,

We are on a roll here! this is perfect yet again...

thanks so much :)

mike123
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 08:08:30
quote:
Originally posted by Peso

For a five-digit password starting with 1
UPDATE	Table1
SET Col1 = 10000 + ABS(CHECKSUM(NEWID())) % 90000



E 12°55'05.63"
N 56°04'39.26"




even better again, thanks!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 08:20:26
This is a stored procedure that can create a complex password for you.
A negative parameter denotes a unique digit of that charactergroup, ie no "gg" for example
CREATE PROCEDURE dbo.uspCreatePassword
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
AS

SET NOCOUNT ON

-- Initialize some variables
DECLARE @UpperCase VARCHAR(26),
@LowerCase VARCHAR(26),
@Numbers VARCHAR(10),
@Special VARCHAR(13),
@Temp VARCHAR(8000),
@Password VARCHAR(8000),
@i SMALLINT,
@c VARCHAR(1),
@v TINYINT

-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
@Numbers = '0123456789',
@Special = '!@#$%&*()_+-=',
@Temp = '',
@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20

IF @UpperCaseItems < -20
SET @UpperCaseItems = -20

IF @LowerCaseItems > 20
SET @LowerCaseItems = 20

IF @LowerCaseItems < -20
SET @LowerCaseItems = -20

IF @NumberItems > 20
SET @NumberItems = 20

IF @NumberItems < -20
SET @NumberItems = -20

IF @SpecialItems > 20
SET @SpecialItems = 20

IF @SpecialItems < -20
SET @SpecialItems = -20

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1,
@c = SUBSTRING(@UpperCase, @v, 1),
@UpperCase = CASE
WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END,
@Temp = @Temp + @c,
@i = @i - 1

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1,
@c = SUBSTRING(@LowerCase, @v, 1),
@LowerCase = CASE
WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END,
@Temp = @Temp + @c,
@i = @i - 1

-- Get the Number Items
SET @i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1,
@c = SUBSTRING(@Numbers, @v, 1),
@Numbers = CASE
WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END,
@Temp = @Temp + @c,
@i = @i - 1

-- Get the Special Items
SET @i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1,
@c = SUBSTRING(@Special, @v, 1),
@Special = CASE
WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END,
@Temp = @Temp + @c,
@i = @i - 1

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1,
@Password = @Password + SUBSTRING(@Temp, @v, 1),
@Temp = STUFF(@Temp, @v, 1, '')

SELECT @Password


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 08:30:19
And if you want it as a function, you will have to "cheat" and make a view first, like this
CREATE VIEW dbo.vwNEWID
AS

SELECT ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) AS nwd
A´nd with this view in place, you can write your function like this
CREATE FUNCTION dbo.fnCreatePassword
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
RETURNS VARCHAR(80)
AS
BEGIN
-- Initialize some variables
DECLARE @UpperCase VARCHAR(26),
@LowerCase VARCHAR(26),
@Numbers VARCHAR(10),
@Special VARCHAR(13),
@Temp VARCHAR(8000),
@Password VARCHAR(8000),
@i SMALLINT,
@c VARCHAR(1),
@v TINYINT

-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
@Numbers = '0123456789',
@Special = '!@#$%&*()_+-=',
@Temp = '',
@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20

IF @UpperCaseItems < -20
SET @UpperCaseItems = -20

IF @LowerCaseItems > 20
SET @LowerCaseItems = 20

IF @LowerCaseItems < -20
SET @LowerCaseItems = -20

IF @NumberItems > 20
SET @NumberItems = 20

IF @NumberItems < -20
SET @NumberItems = -20

IF @SpecialItems > 20
SET @SpecialItems = 20

IF @SpecialItems < -20
SET @SpecialItems = -20

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = nwd % LEN(@UpperCase) + 1,
@c = SUBSTRING(@UpperCase, @v, 1),
@UpperCase = CASE
WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = nwd % LEN(@LowerCase) + 1,
@c = SUBSTRING(@LowerCase, @v, 1),
@LowerCase = CASE
WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Number Items
SET @i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = nwd % LEN(@Numbers) + 1,
@c = SUBSTRING(@Numbers, @v, 1),
@Numbers = CASE
WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Special Items
SET @i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = nwd % LEN(@Special) + 1,
@c = SUBSTRING(@Special, @v, 1),
@Special = CASE
WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = nwd % LEN(@Temp) + 1,
@Password = @Password + SUBSTRING(@Temp, @v, 1),
@Temp = STUFF(@Temp, @v, 1, '')
FROM dbo.vwNEWID

RETURN @Password
END
And all you now have to do is to query the function with something like this
SELECT dbo.fnCreatePassword(2, 2, -2, 0)
meaning
Any 2 UpperCase characters
Any 2 LowerCase characters
Any 2 unique digits
No special characters



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 08:32:41
wow this procedure is above and beyond what I expected.. works perfectly, my perfect solution is even better now :D

thanks again peso !!

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 08:38:28
Also see

http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=78859
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44757


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 09:10:16
Hey Peso,

Very very thorough answer, I evaluated all the options and I think your last suggestion is just perfect.

I am wondering tho, how to update the tables column? For example:

update users set PW2 = (exec uspCreatePassword 0,3,3,0)

not exactly sure how to implement this, if you dont mind helping me out on this final step, that would be awesome!

Thanks again,
mike123
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-29 : 09:44:28
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 10:01:10
quote:
Originally posted by madhivanan

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail



Hi madhivanan,

Appreciate the input, is this what I want to do tho ? I am looking to update a column with the single result brought back by this SPROC.
I am not looking to filter the results.

would this sproc be more functional to me as a function ? (just a guess)

thanks again,
mike123
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-29 : 10:05:35
Something like
update users set PW2 = 
(
Select
*
from
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
Integrated Security=SSPI','Execute yourdb..exec uspCreatePassword 0,3,3,0')
)


Provided procedure returns single value

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 10:41:00
Hi Madhivanan,

Yes it does return a single value. This routine looks like it will work.

Just wondering tho, this SPROC, for this particular application, would it be better as a function ? Is there an easier/cleaner way to accomplish this update?


Thanks again!
mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 10:49:00
See post made 09/29/2008 : 08:30:19



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

- Advertisement -