To begin, we should know under which conditions weak consistency holds. Let's consider the usual case when $X_1,X_2,\ldots$ are i.i.d.r.v.
Since for each $n\in\mathbb N$
$$s^2=\frac1{n-1}\sum_{i=1}^nX_i^2-\frac n{n-1}\bar X^2=\frac n {n-1}\left(\frac1n\sum_{i=1}^nX_i^2-\left(\frac1n\sum_{i=1}^nX_i\right)^2\right).$$
Now, under the hypotheses that allow us to apply the weak or the strong Law of Large Numbers (LLN), we would have
$$\frac1n\sum_{i=1}^nX_i\to E(X_1) \quad (1)$$
and
$$\frac1n\sum_{i=1}^nX_i^2\to E(X_1^2) \quad (2)$$
($X_1$ stands for any other variable; it doesn't matter since they all have identical distribution); these limits could mean convergence in probability or almost sure. By the properties of both types of convergence we have
$$s^2_n=\frac n {n-1}\left(\frac1n\sum_{i=1}^nX_i^2-\left(\frac1n\sum_{i=1}^nX_i\right)^2\right)\to 1\cdot\left(E(X_1^2)-(E(X_1))^2\right).\quad (3)$$
But it happens that neither $(1)$ or $(2)$ need hold with the assumptions so far mentioned.
Now, $(1)$ is true if $X_i$ has a finite first moment (here we have to assume we have a second moment—otherwise there wouldn't be a variance to estimate); and $(2)$ will hold if $X_i^2$ has finite expectation, which again implies finite second moment for $X_i$ (equivalently, $X_i$ has finite variance $\sigma^2=Var(X_1)$). Say $X_i$ has finite momments up to order two, with $E(X_1^k)=m_k<\infty, \,k\le2$, then $(3)$ becomes
$s^2_n\to m_2-m_1^2=\sigma^2,$
and this is true both in probability and almost surely.
So, for i.i.d.v's with finite second momment, $s^2$ is consistent for $\sigma^2$ in strong sense (and so also in weak sense).
If we want an scenario in which consistence is only weak, we may think of a case in which $Var(X_n)=\sigma^2_n$ is not the same for all $n$, but this not only implies that we are not longer assuming identical distributions: in fact, if there is not a unique $\sigma^2$ then there's no point in asking whether $s^2$ is a good estimator for the variance.
Raising independence hypothesis or identical distributions but with a unique variance instead, seems to have less trivial conclusions, but in any case it wouldn't be possible in the case of simple random sampling ($X_n$ i.i.d.).
Best Answer
If you wanted to show only that the sample mean has a smaller variance than every other weighted average of the observations, then this would be an exercise in Lagrange multipliers. But if you want to include all unbiased estimators of $\mu$ based on $X_1,\ldots,X_n$ (for example, the sample median is one such estimator, and is not a weighted average of the observations), then this becomes equivalent to the one-to-one nature of the two-sided Laplace transform.
Observe that the conditional distribution of $(X_1,\ldots,X_n)$ given $\bar X = (X_1+\cdots+X_n)/n$ does not depend on $\mu$. (I could add the details of how to find the conditional distribution if necessary.) In other words, the sample mean $\bar X$ is a sufficient statistic for $\mu$. Therefore, the Rao–Blackwell theorem tells us that any minimum-variance estimator is to be found only among functions of $\bar X$.
Therefore it is enough to show that the only function $g(\bar X)$ of $\bar X$ (where of course, which function $g$ is, is not allowed to depend on $\mu$; i.e. $g(\bar X)$ is actually a statistic) that is an unbiased estimator of $\mu$ is $\bar X$ itself.
The density function of $\bar X$ is $$ x\mapsto \text{constant}\cdot \exp\left(\frac{-1}{2}\cdot\left(\frac{x-\mu}{1/\sqrt{n}}\right)^2\right). $$ In order that the function $g(\bar X)$ be an unbiased estimator of $\mu$, we must $g(\bar X)-\bar X$ being an unbiased estimator of $0$. Let $h(x) = g(x)-x$; then we must have $$ \int_{-\infty}^\infty (\text{same constant})\cdot h(x) \exp\left(\frac{-1}{2}\cdot\left(\frac{x-\mu}{1/\sqrt{n}}\right)^2\right) \, dx = 0 $$ for all values of $\mu$. Hence $$ \text{same constant}\cdot \exp\left(\frac{-n\mu^2}{2}\right) \cdot \int_{-\infty}^\infty \left(h(x) \exp\left(\frac{-n}{2} x^2\right)\right) \exp\left(nx\mu\right) \, dx = 0 $$ regardless of the value of $\mu$. Thus the two-sided Laplace transform of the function $$ x\mapsto h(x)\exp\left( \frac{-nx^2}{2} \right) $$ is $0$ for all values of $\mu$.
Since the two-sided Laplace transform is one-to-one, it can map only one function to the identically zero function.