4/11/2024 0 Comments Sql generate random emailCREATE PROC INT = NVARCHAR(256) = NVARCHAR(50) OUTPUT This code uses 8 bytes, which is much more random than either RAND() or NEWID(). On SQL 2008 and later, you can now use CRYPT_GEN_RANDOM to create up to 8000 bytes of randomness. SELECT += FLOOR(ABS(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) / 9223372036854775808.5) * + 1, 1) FROM sys.objects */Ĭould you please explain the script in details. SELECT * FROM _values A table in the master database that has a variety of constant definitions SELECT number FROM _values WHERE type = 'P' Returns a number sequence 0.2047 SELECT number FROM _values WHERE type = 'P' AND number < Returns a number sequence The query could be rewritten easily (and perhaps more clearly) to use TOP() to determine the password length. , 1) Pick that one character and append it to the password output */ SELECT += FLOOR(ABS(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) / 9223372036854775808.5) * + 1, 1) FROM _values WHERE type = 'P' AND number < /* The table _values has nothing to do with the result, it is just used to make the SELECT statement run the desired number of times. + 1 Final result is a random index (1.Length) to pick one character from SELECT +=. ![]() ![]() * Multiply by the length of the string resulting in 0 <= n < Length FLOOR(.) Convert to integer using FLOOR to get a precise range of 0 <= n < Length. 9223372036854775808.5 Divide by the largest possible value (2^63) to get a number in the range -1.0 < n < 1.0 The BIGINT value will be implicitly converted to NUMERIC(38,18) for maximum precision ABS(.) Get the absolute value resulting in a number in the range 0 <= n < 1.0. AS BIGINT) Convert them to a BIGINT to allow arithmetic operations. NVARCHAR(256) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz.,#$%^&*-+_/=', NVARCHAR(50) OUTPUT AS SET NOCOUNT ON SET = '' /* CRYPT_GEN_RANDOM(8) Generate 8 random bytes CAST(. The default set of characters exclude ones that can be misread (0 and O 1, l, and I). This could include additional punctuation or Unicode chars. INT = 12, - Define the allowable password characters. 12 is the default value if no argument is provided. any hat.ĬREATE PROC dbo.uspRandChars - Set the length for the generated password. There are some people that will sue at the drop of a hat. The major problem is that there is no filter on the possibility of generating some really offensive words as a part of the passwords.īe careful folks. Ralph Wilson warned of the problem again back in 2012. I warned of a major problem with this code and all similar code when it first came out in 2011.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |