What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions?
I would like to create a function that returns a randomly generated varchar(10) using characters from a given string like this one:
"1234567890QWERTYUIOPASDFGHJKLZXCVBNM"
I have my own ideas that work but are not very natural solutions.
The idea behind the function is that if somebody requires a password reset I could do the following:
UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID
A function would be easier to deploy and reuse than a separate table of preallocated passwords.
Best Answer
You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):
If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:
returns:
As you can see, you need to use the format option of
2
on theCONVERT
function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading0x
. But you can also see that even with a single row, multiple calls will get different return values (just likeNEWID()
).Please note the "length" values passed into the
CRYPT_GEN_RANDOM
function. Since a hex byte is 2 characters, you only need to generate a 5-byte value. TheCONVERT(VARCHAR...
uses a "length" of 10 since each hex byte becomes a character. And theCONVERT(NVARCHAR...
uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in_SC
, which is most cases).You can always create your own algorithm and pass in the value of
CRYPT_GEN_RANDOM
for the random aspect. AND, if you can structure your algorithm to be a singleSELECT
such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs:And then run the tests:
And just to be sure about multiple rows in an iTVF:
returns:
Putting all of this info into practice, we can do the following with regards to the original request:
And then execute it as follows:
CAUTION: Please see Item # 2 below related to running this for multiple rows!
You can create a SQLCLR function that would have access to randomizing functionality. The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF.
Update
Given the newly provided context for usage of this function from the Question update:
we can address the following concerns as expressed in a comment on this answer:
True. And it is admittedly slightly inconvenient. However, that does not negate this approach's ability to solve the issue at hand. It just requires some comments / documentation on how to properly use this function.
True. But there is no need to call this function within another function. This doesn't need to be a scalar function in order to be used in an
UPDATE
:With regards to handling multiple rows (please see multi-row test in Option 2 above), functions of any type (user created, at least) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF, or SQLCLR TVF -- are not guaranteed to run per row! You might need to get creative in how you convince the Query Optimizer to not cache the result, but there might be cases when nothing works. The following example shows the same iTVF being called twice. Passing in just
@PasswordLength
causes the output value to be cached. However, when you pass in a formula that incorporates a field from the current row, then it might behave as you are expecting it to:returns:
The point made by @BaconBits in a comment on the question, regarding it being best to not store readable passwords, is a valid and good point. However, circumstances might prevent that option from being possible, or at least possible at this point in time.