It's sort of straight-forward to see why [itex]\sqrt{|det(g)|}[/itex] comes up in 2-dimensions, but I guess the generalization to more dimensions requires the mathematics of forms.
It might be worth looking at how it works in 2 spacelike dimensions. There, you can think of an integral over all space as the limit of a sum, where the sum is over little rectangles with sides [itex]\vec{\delta x}[/itex] and [itex]\vec{\delta y}[/itex]. The issue is: how to compute the area of the rectangle (in 3-D, it would be computing the volume of a parallelepiped, in 4-D, it would be computing the hypervolume of some 4-dimensional cell). In Cartesian coordinates, the displacement vectors [itex]\vec{\delta x}[/itex] and [itex]\vec{\delta y}[/itex] are orthogonal, and the lengths are just [itex]|\delta x|[/itex] and [itex]|\delta y|[/itex], respectively, so you just use the usual formula for area of a rectangle: [itex]A = |\delta x| |\delta y|[/itex]. But if you're using curvilinear coordinates, the two displacements are not necessarily orthogonal. In that case, how do you compute the area?
Well, here's a heuristic argument: In good-old Euclidean space, the area of a parallelogram with sides [itex]\vec{U}[/itex] and [itex]\vec{V}[/itex] is given by:
[itex]A = \vec{U} \times \vec{V} = |U||V| sin(\theta)[/itex] where [itex]\theta[/itex] is the angle between the two sides. We also know that:
[itex]\vec{U} \cdot \vec{V} = |U||V| cos(\theta)[/itex]
So we can relate the cross product to the dot product as follows:
[itex]|\vec{U} \times \vec{V}|^2 = |U|^2 |V|^2 sin^2(\theta) = |U|^2 |V|^2 - |U|^2 |V|^2 cos^2(\theta) = (\vec{U} \cdot \vec{U}) (\vec{V} \cdot \vec{V}) - (\vec{U} \cdot \vec{V})^2[/itex]
The dot-product can be written in terms of the metric:
[itex]\vec{U} \cdot \vec{V} = g_{\mu \nu} U^\mu V^\nu[/itex]
So we can rewrite the cross-product in terms of the metric:
[itex]|\vec{U} \times \vec{V}|^2 = (g_{\mu \nu} g_{\alpha \beta} - g_{\mu \alpha} g_{\nu \beta}) U^\mu U^\nu V^\alpha V^\beta[/itex]
Now, let's specialize to the case [itex]\vec{U} = \vec{\delta x}[/itex] and [itex]\vec{V} = \vec{\delta y}[/itex]. In that case, [itex]U^x = \delta x, U^y = 0, V^x = 0, V^y = \delta y[/itex]. So we have:
[itex]|\vec{\delta x} \times \vec{\delta y}|^2 = (g_{x x} g_{y y} - g_{x y} g_{x y}) \delta x^2 \delta y^2[/itex] (All other terms are zero)
Note that the expression involving the metric is just the determinate of the 2x2 matrix:
[itex]\left( \begin{array} \\ g_{x x} & g_{x y} \\ g_{y x} & g_{y y} \end{array} \right)[/itex]
So,
[itex]|\vec{\delta x} \times \vec{\delta y}|^2 = det(g) \delta x^2 \delta y^2[/itex]
So,
[itex]|\vec{\delta x} \times \vec{\delta y}| = \sqrt{det(g)} \delta x \delta y[/itex]
It would take a lot more work to see that this generalizes to more than 2 dimensions and to the case of a pseudo-Euclidean metric, but it gives you a little bit of the flavor for why something like [itex]\sqrt{det(g)}[/itex] might show up.