How about something like this? You can do a table of numbers to generate a set of them with the required distribution.

DECLARE @i float = RAND(CHECKSUM(NEWID()))

SELECT CASE WHEN @i <= 0.4 THEN 1

WHEN @i > 0.4 AND @i <= 0.65 THEN 2

WHEN @i > 0.65 AND @i <= 0.85 THEN 3

WHEN @i > 0.85 AND @i <= 0.99 THEN 4

WHEN @i > 0.99 THEN 5

END