Os dejo una función para generar passwords aleatorios de texto de diferentes longitudes, se puede modificar fácilmente para que sólo genere números, símbolos, etc..

CREATE VIEW myRndNumber
AS
SELECT Rand() AS Random

CREATE FUNCTION [dbo].[fnGenerateRandomText]
(
-- Add the parameters for the function here
@Length int
)
RETURNS nvarchar(100)
AS

BEGIN

DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN

SET @RandomNumber = (SELECT TOP 1 Random FROM myRndNumber)
SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

SET @counter = @counter + 1

SET @RandomID = @RandomID + @CurrentCharacter

END

RETURN @RandomID
END

Para utilizar esta función simplemente habría que llamarla de la siguiente forma:

DECLARE @clave as varchar(10)

set @clave = dbo.[fnGenerateRandomText](10)

Print @clave