A morphism $f:X\rightarrow Y$ of schemes gives a faithful pullback functor $f^*$ exactly when the morphism $f$ is surjective on underlying sets. This can be seen in several steps.
Note that the functor is faithful iff it preserves zero; that is, $f^*(M)=0$ implies $M=0$.
a.To see this, note that if a right exact functor preserves zero, then any morphism which becomes the zero map was already the zero map (consider its cokernel). b. Then, if two maps $g,g'$ go to the same map, their difference goes to the zero map, and thus the difference was already zero.A module $M$ on $Y$ is zero iff $Hom_Y(\\mathcal{O}_Z,M)=0$$Hom_Y(\mathcal{O}_Z,M)=0{}{}{}$ for every irreducible closed subscheme $Z$ (that is, for the closure of every point).
If $Hom_Y(\mathcal{O}_Z,f^*(M))=0$, then $Hom_X(\mathcal{O}_{f(Z)},M)=0$.
Now, if every point in $X$ is the image of a point in $Y$, and $f^*(M)=0$, then by 2 and 3, $Hom_X(\mathcal{O}_{Z'},M)=0$ for every point $Z'\in X$. Then, by $2$, we know that $M=0$, and so $f^* $ preserves zero. Thus, by 1, $f^* $ is faithful. If $f$ is not surjective on underlying sets, then pulling back the skyscraper sheaf of a missed point will be zero, and so by 1, it cannot be faithful.