I believe with a fairly standard scaling and shifting, we can recover a normal limit for any $0 < \alpha < 1$ by a standard application of the Lindeberg–Feller theorem.

Define the triangular array of random variables $X_{n,m}$, $1 \leq m \leq n$ such that each row contains iid elements distributed as $\mathrm{Bernoulli}(cn^{-\alpha})$ random variables. Then $S_n = \sum_{m=1}^n X_{n,m}$ is $B(n,p)$ with $p = c n^{-\alpha}$.

Now, shift and rescale by taking $Y_{n,m} = n^{-(1-\alpha)/2} (X_{n,m} - c n^{-\alpha})$.
Note that $\mathbb E Y_{n,m} = 0$ and $\mathbb E Y_{n,m}^2 = n^{-1} c (1 - c n^{-\alpha})$.

We need only check the Lindeberg–Feller conditions:

- $\sum_{m=1}^n \mathbb E Y_{n,m}^2 = c (1-cn^{-\alpha}) \to c > 0$, and
- For all $\epsilon > 0$, $\lim_{n\to\infty} \sum_{m=1}^n \mathbb E |Y_{n,m}|^2 1_{(|Y_{n,m}|^2 > \epsilon)} = 0$.

The second one follows since $1_{(|Y_{n,m}|^2 > \epsilon)} = 0$ for all sufficiently large $n$ since $|Y_{n,m}|^2 \leq n^{-(1-\alpha)} (1+c)^2$ almost surely.

Hence $n^{-(1-\alpha)/2} (S_n - c n^{1-\alpha}) \;\xrightarrow{\;d\;}\; \mathcal N(0,c)$.

To see what fails in the case where $\alpha = 1$, note that the indicator function $1_{(|Y_{n,m}|^2 > \epsilon)}$ will no longer go to zero almost surely. Therefore the second condition will fail because there will be a contribution of $c n^{-1} (1-c/n)^2$ in expectation for each of the $n$ terms, which, when added up yields (obviously) a nonzero limit.