One question I saw in one of the SQL Server forums I usually visit is how to generate a random password using a user-defined function. Here’s my version of the random password generator user-defined function.
Using the trick in the tip of the day entitled, “How to Call NEWID() Function in a User-Defined Function”, I was able to come up with the following user-defined function that generates a random password.
As discussed on the said tip of the day, I will need to call the NEWID() function within the user-defined function. Since I cannot call the NEWID() function directly from the user-defined function, I need to create a view first:
CREATE VIEW [dbo].[RandomNewID]
AS
SELECT NEWID() AS [NewID]
GO
Now that I have this view, I can now create my user-defined function that will generate a random password. To make the function more flexible, it accepts the number of characters that the user wants for the password:
CREATE FUNCTION [dbo].[ufn_GeneratePassword] ( @PasswordLength INT )
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @Password VARCHAR(20)
DECLARE @ValidCharacters VARCHAR(100)
DECLARE @PasswordIndex INT
DECLARE @CharacterLocation INT
SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890'
SET @PasswordIndex = 1
SET @Password = ''
WHILE @PasswordIndex <= @PasswordLength
BEGIN
SELECT @CharacterLocation = ABS(CAST(CAST([NewID] AS VARBINARY) AS INT)) %
LEN(@ValidCharacters) + 1
FROM [dbo].[RandomNewID]
SET @Password = @Password + SUBSTRING(@ValidCharacters, @CharacterLocation, 1)
SET @PasswordIndex = @PasswordIndex + 1
END
RETURN @Password
END
Here are some sample output using the generate password function:
SELECT [dbo].[ufn_GeneratePassword] ( 8 ) AS [Password8],
[dbo].[ufn_GeneratePassword] ( 10 ) AS [Password10],
[dbo].[ufn_GeneratePassword] ( 12 ) AS [Password12]
Password8 Password10 Password12
--------- ----------- -------------
rj58PffA 0x8dwB9peF iuLysW0zA2jy