rand

Returns a random value between 0 and 1, which is generated using the specified 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 pseudo-random 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 pseudo-random 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.
For general information about mathematical functions, see “Mathematical functions”.

SQL92 – Compliance level: Transact-SQL extension.

Any user can execute rand.