My feeling is that this is really about set-theoretic complete intersections.

Let $X={\rm Spec}\\, A$ be a noetherian affine scheme such that every irreducible subscheme of $X$ is a set-theoretic complete intersection. In other words, for any prime $\mathfrak p\subset A$, there exist a set of elements $x_1,\dots,x_t\in \mathfrak p$ such that $t={\rm ht} (\mathfrak p)$ and the $x_1,\dots,x_t$  generate a $\mathfrak p$-primary ideal with $\mathfrak p= \sqrt{\( x_1,\dots, x_t\)}$, or equivalently the zero set $Z(x_1,\dots,x_t)=\overline{\{\mathfrak p\}}$.

In this case, take $M=A/\(x_1,\dots,x_t\)$ has the property that ${\rm Ass} (M)=\{\mathfrak p\}$.

If in addition $A$ is CM, then so is $M$ and then its projective dimension satisfies
that
$$
{\rm pd} (M)=\dim A-\dim M= {\rm ht} (\mathfrak p).
$$

I suppose the next step is to look at an affine scheme with an irreducible subscheme that is not a set-theoretic complete intersection and see what happens there. 

(**Addendum**)

Regarding the case when there exists an irreducible subscheme that is not a set-theoretic complete intersection, one may mention, that in general, (still assuming that $A$ is CM, which follows if it is regular),
$$
{\rm pd} (M)=\dim A-{\rm depth}_A M.
$$
If furthermore ${\rm Ass} (M)=\{\mathfrak p\}$, then it follows that 
$$
{\rm depth}_A M \leq \dim M = \dim A - {\rm ht} (\mathfrak p),
$$
So ${\rm pd} (M)\geq {\rm ht} (\mathfrak p)$ with equality iff $M$ is CM.
In other words, your desired condition is to find a CM module whose only associated prime is $\mathfrak p$.

At least for modules generated by a single element this seems to be pretty close to $\overline{\{\mathfrak p\}}$ being a set-theoretic complete intersection as 
for an ideal $\mathfrak q\subseteq A$ in a noetherian ring the following holds:
$$
\mathfrak q \text{ is $\mathfrak p$-primary} \Leftrightarrow {\rm Ass}(A/\mathfrak q)=\{\mathfrak p\}.
$$
So, in order to ensure that $A/\mathfrak q$ is CM one would need the right number of generators and in order to have the condition on the associated primes one would need that $\mathfrak q$ is $\mathfrak p$-primary. Of course, I am not claiming that this is the only way to produce such modules, but this seems to be the obvious way.