Its very unlikely for a comet to become a satellite of an inner solar system planet. Much less likely than it is for an asteroid. Most asteroids are on fairly circular orbits, and so the relative velocity between asteroids and planets is quite low. In comparison comets have very elliptical orbits, and their relative velocities to the planets are much larger.
For an asteroid to be captured it must lose momentum. This is possible, though rare. For example, a binary asteroid can be captured if it is separated by tidal forces. For an comet with much more momentum, the chance of being captured is much much lower. Asteroids are captured by the Earth moon system, but not into stable orbits, they don't stay long.
If it did occur, the comet would still be active, with a coma of gas, which would be visible just like a very nearby comet. It wouldn't be particularly bright, since the surface brightness of a comet doesn't depend on distance from the Earth.
Over time the comet would run out of volatiles and become more or less indistinguishable from a captured asteroid. If it were in the Earth's orbit it probably wouldn't last that long, as there are not many orbits that are stable in the long term around the Earth, due to perturbations from the moon.
The dust and gas, including water vapour, will initally remain in orbit, forming a faint ring. It will, over time, be disrupted, and either end up in the atmosphere, on the moon, or ejected from the system. A comet doesn't contain enough water to make a difference to the Earth's ecosystem.