Generate password in SQL

Here is a nice little stored procedure to let you generate a random strong password. I have used this to help automate the automatic changing of the SA password for all production SQL servers every 30 days.

The process looks up the SA password for the given server from a secure table and then connects to the server. Then this store procedure is used to generate a new password which is then applied to the SA account and saved in the secure location.

This process replaced the manual process of logging into 30 different production servers every month and doing it all by hand. Worked very well.

Here is the code for the stored procedure. As always code is provided as is. Use with care.

CREATE PROCEDURE GenPassWord
@len INT = 8
AS

-- Creates a complex password of the passed in lenght.
-- If no lenght passed in, defaults to 8 characters

DECLARE @password varchar(25)
DECLARE @type tinyintDECLARE @bitmap char(6)

SET @password=''

WHILE @len > 0
BEGIN

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

--Here's the result
SELECT @password