Professional Web Applications Themes

Creating Random Numbers or Characters - Microsoft SQL / MS SQL Server

Hi, Can MS SQL server create random numbers (or characters) as default records? When a user create a username, I would like to have a randomized password automatically created. Please let me know if there is a way to do that. Cyont...

  1. #1

    Default Creating Random Numbers or Characters

    Hi,
    Can MS SQL server create random numbers (or characters) as default records?
    When a user create a username, I would like to have a randomized password
    automatically created.

    Please let me know if there is a way to do that.

    Cyont


    Cyont Guest

  2. #2

    Default Re: Creating Random Numbers or Characters

    This is not my work.
    I forget who created the original.
    Sorry that I cannot credit the original author!


    if exists (select * from sysobjects where id = object_id('foo'))
    drop procedure foo
    GO
    CREATE PROCEDURE foo
    len int,
    password varchar(50) OUTPUT
    AS


    IF len > 50
    BEGIN
    PRINT 'password cannot be larger than 50 chars'
    RETURN (1)
    END

    DECLARE type tinyint
    SET password=''

    WHILE len > 0
    BEGIN
    --Appending a random alphabet
    SET type = ROUND(1 + (RAND() * (3)),0)

    IF type = 1 --Appending a random lower case alphabet to password
    SET password = password + CHAR(ROUND(97 + (RAND() * (25)),0))
    ELSE IF type = 2 --Appending a random upper case alphabet to password
    SET password = password + CHAR(ROUND(65 + (RAND() * (25)),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(33 + (RAND() * (13)),0))

    SET len = len - 1
    END
    RETURN (0)
    GO

    --examples:
    DECLARE password varchar(50)
    EXEC foo 5, password OUTPUT
    SELECT password AS ThePAssword
    GO
    DECLARE password varchar(50)
    EXEC foo 20, password OUTPUT
    SELECT password AS ThePAssword
    GO


    --
    Keith

    "Cyont" <com> wrote in message news:#phx.gbl... 
    Keith Guest

  3. #3

    Default Re: Creating Random Numbers or Characters

    Hi Cyont,

    You can use RAND(SeedValue)

    But, you need to make sure you supply different seedvalue each time.
    Another alternative is to use NewID() . This generates a GUID. If you
    think GUID is long then you may want to do some kinda chopping and
    concatenating with another string etc.

    --
    -Ram Thiru
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm


    "Cyont" <com> wrote in message
    news:%phx.gbl... 
    records? 


    Ram Guest

  4. #4

    Default Re: Creating Random Numbers or Characters

    This will set up the ability to create a case-sensitive, alphanumeric
    password.

    CREATE TABLE dbo.passwordCharacters
    (
    c CHAR(1)
    )


    DECLARE i INT
    SET i = 0
    WHILE i <= 9
    BEGIN
    INSERT dbo.passwordCharacters
    VALUES(RTRIM(i))
    SET i = i + 1
    END

    SET i = 65
    WHILE i <= 122
    BEGIN
    IF i <= 90 OR i >= 97
    INSERT dbo.passwordCharacters
    VALUES(CHAR(i))
    SET i = i + 1
    END




    Now, your stored procedure:




    CREATE PROCEDURE dbo.generatePassword
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE password VARCHAR(36)
    SET password = ''
    SELECT password = COALESCE(password, '')+c
    FROM (SELECT TOP 8 c FROM dbo.passwordCharacters
    ORDER BY NEWID()) x
    SELECT password AS 'password' -- or use an OUTPUT param
    END
    GO


    You can change the TOP in the subquery for the number of characters, or you
    can drop the TOP altogether and truncate the result at the receiving end.

    Note that this will not repeat a character... if you want repeats to be
    possible, you can insert the same letters multiple times. You could even go
    so far as to make letters more likely than numbers, etc.




    "Cyont" <com> wrote in message
    news:#phx.gbl... 
    records? 


    Aaron Guest

  5. #5

    Default Creating Random Numbers or Characters

    You can create passwords from the unique SID for the
    login, though, as will be scripted with the following:

    declare PWLength int
    select PWLength = 12 -- set to whatever you want
    select 'exec sp_password ' + ' null, ' +
    master.dbo.fn_varbintohexstr( convert(varbinary
    (8000),substring(reverse(sid), 1, PWLength)) ) + ', ' +
    LoginName
    from master.dbo.syslogins
     
    as default records? 
    randomized password 
    Art Guest

Similar Threads

  1. Random Numbers
    By Ricardo in forum PHP Development
    Replies: 12
    Last Post: July 14th, 07:31 PM
  2. creating random numbers for display
    By lickteig in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: March 1st, 04:51 PM
  3. getting a list of random numbers
    By Thomas L in forum PHP Development
    Replies: 3
    Last Post: January 3rd, 01:33 PM
  4. Random Numbers - Please help!
    By djmasala.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: August 21st, 09:24 AM
  5. Generating random numbers?
    By Harry Ohlsen in forum Ruby
    Replies: 14
    Last Post: July 31st, 05:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139