Populate table with randomly generated password

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

SQL Server Development (2000)

Populate table with randomly generated password

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-13 : 08:06:29

Kevin writes "Hi,

I am looking to populate a password field in a table that consists of only three fields: pernr, userid, password. This table is used as a base table for a web app. Whenever someone within the company wants to login to an internal web app they retrieve their password from this underlying SQL table. Every night a run a DTS package that adds rows or deletes rows of employees who just started with the company or who are leaving. For those that are starting, I populate the pernr and userid fields from base tables in the database. The password field is a randomly generated 9 digit field. I have copied script from another site written by Narayana Vyas Kondreddithat. It is a stored procedure that can output what i need.

EXAMPLE: CREATE PROC random_passwordcode
(
@len int = 9, --Length of the password to be generated
@password_type char(7) = 'complex'
)
AS
BEGIN
DECLARE @password varchar(25), @type tinyint
SET @password=''
WHILE @len > 0
BEGIN
IF @password_type = 'complex'
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
END
SET @len = @len - 1
END
SELECT @password --Here's the result
END

exec random_passwordcode

Now what I want to do is loop through this code every time I add a new row (some nights we don't add any rows, other nights we may add 10 rows) to my table to populate a random password for each new employee. Can someone please help me with this. Do I use a curso? A trigger? Could I simplify this code to work in an update statement? Thanks for any help you can give."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources