Generally, these terms are used to refer to lone pairs rather than s-orbitals.
In the context of VSEPR theory, lone pairs usually affect molecular geometry. For example $\ce{NH3}$ adopts a trigonal pyramidal structure, because of the presence of the lone pair on nitrogen; if there was no lone pair, then it would be trigonal planar, cf. $\ce{BF3}$. These are so-called stereochemically active lone pairs, since they affect the stereochemistry at nitrogen.
In a few rare cases lone pairs do not affect the molecular geometry, and these are termed stereochemically inactive. The only example I can think of, off the top of my head, is in $\ce{[XeF8]^2-}$, which should have 8 bond pairs and 1 lone pair around xenon. Despite the presence of the lone pair, the dianion adopts a square antiprismatic structure, without any obvious position in space for the lone pair to occupy. Looking in Shriver and Atkins' Inorganic Chemistry (6th ed.) there are a couple more example of $\ce{[SeF6]^2-}$ and $\ce{[TeCl6]^2-}$, which both have octahedral geometries despite having six bond pairs and one lone pair around the central atom.
In VSEPR theory, these stereochemically inactive lone pairs are rationalised by placing them in an s-orbital, which is non-directional. An electron pair in an s-orbital has a spherically symmetric electron density, and hence cannot influence the shape of a molecule. However, it is not exactly the orbital that is stereochemically (in)active, it is the electrons, and hence the more frequently seen term is "stereochemically (in)active lone pair".
There is one more context which should be mentioned, because it occurs frequently, and that is in tin(II) and lead(II) compounds. The electronic configurations of these two ions end with $n\mathrm{s^2}$, and this s-orbital lone pair tends to generate some distortions in stereochemistry.
A great example is that of the oxides. Solid $\ce{MgO}$ has a structure in which $\ce{Mg^2+}$ is octahedrally coordinated by six $\ce{O^2-}$ ions (rock salt/halite structure), but $\ce{SnO}$ and $\ce{PbO}$ have an unevenly distributed, square pyramidal coordination of $\ce{M^2+}$ by four $\ce{O^2-}$ ions. Traditionally this is attributed to the presence of a stereochemically active lone pair on Sn(II) or Pb(II).
This description cannot actually be complete, though, because if the geometry is solely determined by the lone pair on the metal, then one would expect PbS to have the same structure - and it doesn't. In fact, PbS has the same structure as MgO, with regular octahedral coordination, so clearly the anion has a role to play. A full explanation is beyond the scope of this answer, but one way of rationalising this is by invoking a second-order Jahn–Teller distortion. The interested reader may consult Chem. Soc. Rev. 2011, 40 (9), 4455–4463.
However, the "stereochemically active lone pair" is a recurrent theme in Sn(II) and Pb(II) structural chemistry (sometimes Tl(I) and Bi(III) also). For example, the metallocenes $\ce{SnCp2}$ and $\ce{PbCp2}$ adopt a bent structure, rather than the linear structure of $\ce{MgCp2}$ (and the more famous $\ce{FeCp2}$). Again, though, VSEPR theory is not the best way of explaining any of this; a proper explanation would involve MO theory.
