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\}. $$ One way to ensure that $A/\mathfrak q$ is CM is to make sure that $\mathfrak q$ has 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.