How to Generate Random Password in SQL Server? – Interview Question of the Week #181

Question: How to Generate Random Password in SQL Server?

Answer: This is a fantastic question and here is the script to generate a random password in SQL Server

DECLARE @char CHAR = ''
DECLARE @charI INT = 0
DECLARE @password VARCHAR(100) = ''
DECLARE @len INT = 12 -- Length of Password
WHILE @len > 0
BEGIN
SET @charI = ROUND(RAND()*100,0)
SET @char = CHAR(@charI)

IF @charI > 48 AND @charI < 122
BEGIN
SET @password += @char
SET @len = @len - 1
END
END
SELECT @password [PassWord]

Well, that’s it. It will generate a password with alphabets, numerics, and special characters. If you want to change the length of the password, you can easily do so by changing the parameter @len. Please note that this function is based on the random function so you will probably never get the same password again in the near future. Here are the sample 10 passwords I generated from the above script to give you an idea what the final outcome looks like.

TUC:GR8?MHYP
6I9G2B]LG3H2
U8OO:^7DE;cb
93RW4]>VI`L9
[V?W^;M1E4Oc

Here are few another related blog post on the similar subject:

• How to Enforce Password Policy of Windows to SQL Server? – Interview Question of the Week #142
• MySQL – Fix – Error – Your Password does not Satisfy the Current Policy Requirements
• SQL SERVER – Best Practices About SQL Server Service Account and Password Management
• SQL SERVER – Change Password of SA Login Using Management Studio
• When was Domain Account Password Changed in SQL Server? – Interview Question of the Week #093
• SQL SERVER – Forgot the Password of Username SA

If you know any other way to generate a random password, please share it and I will publish with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)