In theory, most neural networks can approximate any continuous function on compact subsets of $\mathbb{R}^n$, provided that the activation functions satisfy certain mild conditions. This is known as the universal approximation theorem (UAT), but that should not be called universal, given that there are a lot more discontinuous functions than continuous ones, although certain discontinuous functions can be approximated by continuous ones. The UAT shows the theoretical powerfulness of neural networks and their purpose. They represent and approximate functions. If you want to know more about the details of the UAT, for different neural network architectures, see this answer.
However, in practice, neural networks trained with gradient descent and backpropagation face several issues and challenges, some of which are due to the training procedure and not just the architecture of the neural network or available data.
For example, it is well known that neural networks are prone to catastrophic forgetting (or interference), which means that they aren't particularly suited for incremental learning tasks, although some more sophisticated incremental learning algorithms based on neural networks have already been developed.
Neural networks can also be sensitive to their inputs, i.e. a small change in the inputs can drastically change the output (or answer) of the neural network. This is partially due to the fact that they learn a function that isn't really the function you expect them to learn. So, a system based on such a neural network can potentially be hacked or fooled, so they are probably not well suited for safety-critical applications. This issue is related to the low interpretability and explainability of neural networks, i.e. they are often denoted as black-box models.
Bayesian neural networks (BNNs) can potentially mitigate these problems, but they are unlikely to be the ultimate or complete solution. Bayesian neural networks maintain a distribution for each of the units (or neurons), rather than a point estimate. In principle, this can provide more uncertainty guarantees, but, in practice, this is not yet the case.
Furthermore, neural networks often require a lot of data in order to approximate the desired function accurately, so in cases where data is scarce neural networks may not be appropriate. Moreover, the training of neural networks (especially, deep architectures) also requires a lot of computational resources. Inference can also be sometimes problematic, when you need real-time predictions, as it can also be expensive.
To conclude, neural networks are just function approximators, i.e. they approximate a specific function (or set of functions, in the case of Bayesian neural networks), given a specific configuration of the parameters. They can't do more than that. They cannot magically do something that they have not been trained to do, and it is usually the case that you don't really know the specific function the neural network is representing (hence the expression black-box model), apart from knowing your training dataset, which can also contain spurious information, among other issues.