Assume that $X$ has a normal distribution with mean $\mu=0$ and variance $\sigma^2$. Then the probability density function (pdf) of the random variable $X$ is given by:
\begin{eqnarray*}
f_X(x)=\frac{1}{\sqrt{2\pi}\sigma}e^{-\frac{x^{2}}{2\sigma^{2}}}
\end{eqnarray*}
for $-\infty<x<\infty$ and $\sigma>0$.
Now, when $Z$ has a standard normal distribution, $\mu=0$ and $\sigma^2=1$, so, it's pdf is given by:
\begin{eqnarray*}
f_Z(z)=\frac{1}{\sqrt{2\pi}}e^{-\frac{x^{2}}{2}}
\end{eqnarray*}
for $-\infty<z<\infty$.
If we then multiply $Z$ by the standard deviation $\sigma$ and let that be equal to the function $g(Z)$, (i.e. $Y=g(Z)=\sigma Z$) we can use the formula for transforming functions of random variables (see Casella and Berger (2002), Theorem 2.1.8):
\begin{eqnarray*}
{f_Y(y)=f_Z(z)(g^{-1}(y))}{{d\over{dy}}{g^{-1}(y)}}
\end{eqnarray*}
First we find $Z=g^{-1}(y)={y\over{\sigma}}$ and ${d\over{dy}}{g^{-1}(y)}={1\over{\sigma}}$.
So, substituting these terms, we have:
\begin{eqnarray*}
f_{Y}(y) & = & f_{Z}\left(g^{-1}(y)\right){\frac{d}{{dy}}{g^{-1}(y)}}\\
& = & f_{Z}\left(\frac{y}{\sigma}\right)\frac{1}{\sigma}\\
& = & \frac{1}{{\sqrt{2\pi}}}{e^{-}\frac{\left(\frac{y}{\sigma}\right)^{2}}{{2}}}\left(\frac{1}{\sigma}\right)\\
& = & \frac{1}{{\sqrt{2\pi}}}\left(\frac{1}{\sigma}\right){e^{-}\frac{y^2}{{2\sigma^{2}}}}\\
& = & \frac{1}{{\sqrt{2\pi}\sigma}}{e^{-\frac{y^2}{{2\sigma^{2}}}}}
\end{eqnarray*}
This PDF is identical to the PDF of $f_X$ given at the beginning of the proof which is simply the pdf of a normal random variable with mean $\mu=0$ and variance $\sigma^2$. Hence, $Y\sim N(0, \sigma^2)$. So if you look closely back through the proof, you'll see that the squared $\sigma$ exponent term is introduced through the original squared $x$ term via composite functions with the inner function being the inverse of transformation. So this is how multiplying by $\sigma$ introduces $\sigma^2$ into the the pdf.
What you are told is only relevant when you are sampling from a finite population of size $N$, with simple random sampling without replacement. For most applications, there is no definite finite population, so what you are told is irrelevant. It is also irrelevant when you are samling with replacement.
When relevant, there is a finite population correction explained here: Explanation of finite correction factor and here for more details, a web pdf.
But, even if you think you have a finite population, it might not be relevant. Most uses of statistics are analytic, not enumerative. So if you are sampling from this years hospital patients, presumably a finite population, presumably you are not only interested in that specific population, but want to generalize to a larger population from which that one was drawn, from which next year patients will be drawn, ... and then the finite population aspects are irrelevant.
Best Answer
If you can sample from a given distribution with mean 0 and variance 1, then you can easily sample from a scale-location transformation of that distribution, which has mean $\mu$ and variance $\sigma^2$. If $x$ is a sample from a mean 0 and variance 1 distribution then $$\sigma x + \mu$$ is a sample with mean $\mu$ and variance $\sigma^2$. So, all you have to do is to scale the variable by the standard deviation $\sigma$ (square root of the variance) before adding the mean $\mu$.
How you actually get a simulation from a normal distribution with mean 0 and variance 1 is a different story. It's fun and interesting to know how to implement such things, but whether you use a statistical package or programming language or not, I will recommend that you obtain and use a suitable function or library for the random number generation. If you want advice on what library to use you might want to add specific information on which programming language(s) you are using.
Edit: In the light of the comments, some other answers and the fact that Fixee accepted this answer, I will give some more details on how one can use transformations of uniform variables to produce normal variables.
At the end of the day, a correctly implemented method is not better than the uniform pseudo random number generator used. Personally, I prefer to rely on special purpose libraries that I believe are trustworthy. I almost always rely on the methods implemented in R either directly in R or via the API in C/C++. Obviously, this is not a solution for everybody, but I am not familiar enough with other libraries to recommend alternatives.