rand

Returns a random float value between 0 and 1 using the specified (optional) integer as a seed value.

rand([integer])

integer: is any integer (tinyint, smallint, or int) column name, variable, constant expression, or a combination of these.

select rand()

--------------------
           0.395740

declare @seed int
select @seed=100
select rand(@seed)

-------------------- 
             0.000783

rand, a mathematical function, returns a random float value between 0 and 1, using the optional integer as a seed value.
The rand function uses the output of a 32-bit pseudorandom integer generator. The integer is divided by the maximum 32-bit integer to give a double value between 0.0 and 1.0. The rand function is seeded randomly at server start-up, so getting the same sequence of random numbers is unlikely, unless the user first initializes this function with a constant seed value. The rand function is a global resource. Multiple users calling the rand function progress along a single stream of pseudorandom values. If a repeatable series of random numbers is needed, the user must assure that the function is seeded with the same value initially and that no other user calls rand while the repeatable sequence is desired.

ANSI SQL – Compliance level: Transact-SQL extension.

Any user can execute rand.