If you are connected to the grid, the power you use is effectively generated by the power plant with the highest marginal costs. That often means fossil fuels. It doesn't matter whether you are buying generic power from the grid, or if you have a contract stating that the power you use is generated by renewables or nuclear. If you disconnect from the grid, the overall demand goes down, and some of the most expensive generation capacity is no longer needed.
In the long term, if there are many customers buying explicitly carbon-free power from the grid, it may increase the construction of new carbon-free generation capacity. But that effect is difficult to quantify.