There are lots of ways of generating random strings in SQL Server. But instead of spending much time on finding solution to random password generation, here is an easy way to do that.

SELECT SUBSTRING(convert(varchar(50),NEWID()),0,8) as password

This generates 8 charcters long  password which is a random string.

Some results:-

673CB61

6BD26C7

7F5F1EB