You've got to understand that this one incident was no small matter in God's eyes. I mean look how many God-pleasing things Moses did in his life. A pretty good track record, right? Yet, if God was basing faith upon personal worthiness and spiritual merit badges, then how come God told Moses that it was because of this incident that he would not personally enter the promised land?
In God's eyes, it was a scandalous lack of faith...instead of trusting and taking God at his word, Moses struck the rock in a manner of his own choosing.
I am merely making the point that Brigham young is similar to Moses. Both are not infallible. It is not a requisite of being an apostle or prophet.