With many of the IS lenses it's not necessary to turn off IS manually when you are using the lens on a tripod... The IS senses lack of movement and turns itself off when it's not needed. The lenses that do this include: 24-105, all 70-200s with IS, 200/2, 300/2.8, 400/4, 400/2.8, 500/4, 600/4 and 800/5.6.
There are also some Canon lenses that use a simpler form of IS and should to be turned off when locked down on a solid tripod, to prevent the IS from going into sort of a feedback loop where the IS is actually causing movement that will show up as blurring in the image. You can see this effect happening in the viewfinder, so the lens will remind you if you forget! The lens and it's IS system will not be damaged in any way, but the movement induced by IS in these lenses in this situation can effect images. Lenses in this category include: 28-135 IS, 70-300 IS, 300/4 IS, 100-400 IS. I am certain there are more, but don't know of a complete list. I would assume that most relatively inexpensive lenses with IS would have this type of IS (probably the 18-55 IS and 18-135 IS kit lenses, for example, though I've never seen them listed anywhere).
If you are using the tripod "loose", panning with it and/or using a gimbal head, leave the IS on.
If you are using a monopod, it's usually best to leave the IS on.
IS can be effective even when using shutter speeds such as 1/500, 1/640, 1/800, so I'd leave it on.
I also wouldn't call it "unnecessary" on a tripod by any means. There are many things that can cause minor vibrations within the camera... before IS we used to go to great lengths to stabilize long telephotos and high magnification macro shots. Pressing the shutter release, the slap of the mirror within the camera, even a slight breeze and many more things can cause loss of sharpness in images due to slight movement and blur.
If you are making particularly long exposures (over, say, 2 or 4 seconds), you probably should turn IS off, just to be certain it doesn't "jump" during the exposure, though I really think there's little risk of this occuring.
I don't shoot video with DSLRs, so really can't say how IS effects video. But I suspect it would be effective, since it was originally a feature of video cameras, before Canon started implementing in still camera lenses too. My only concern with video would be noise that IS generates being picked up on the soundtrack.
When using the super telephotos with IS - 200/2, 300/2.8, 400/4, 400/2.8, 500/4, 600/4, 800/5.6 - with exposures between 2 seconds and 1/15, IS can be effective reducing "mirror slap" vibrations in the camera and I'd leave it on. (With longer exposures any shutter slap vibration is an insignificant portion of the overall exposure, so it might be turned off if one wishes, but that would only possibly serve to save a little battery power.)
Frankly, it works out that the lenses that I use predominently handheld are the type that have the simpler IS and the lenses I virtually always put on a tripod are the type that auto sense when there's no movement and turn off IS themselves. So, ten + years using various IS lenses now, I very infrequently bother to turn it off. Only on rare occasion have I seen a lens get into the feedback loop, which immediately reminded me to turn off IS manually. IS really doesn't draw very much battery power, either, so there's little saved by turning it off.
Oh, and if panning with IS on, where you are using a slower shutter speed to deliberately blur down the background, many lenses have a Mode 2 setting that turns off IS in the horizontal axis, but leaves it on and effective in the vertical axis. The orientation of the camera and lens (landscape or portrait) doesn't matter... the lens senses what orientation it's in and only turns off the actual horizontal axis, in either orientation. AFAIK, there is no setting that would turn off IS only on the vertical axis, leaving it on for the horizontal axis. But I really can't think of any reason for doing so either... can't imagine ever doing "vertical panning" for any reason.
IS can be effective in many situations, so IMO it's best to just leave it on, except when locking the lens down quite solidly on a tripod (and I'd make a point of leaving it on even then, if using any of the "super telephotos" mentioned above).
I have no idea about Sigma OS or Tamron VC systems, how they work or if they need to be turned off in certain situations. In general, though, I wouldn't worry too much. Both OS and VC are much more recently developed than some of the Canon IS lenses (within the past 3 to5 years vs some Canon lenses that saw IS implemented around 15 years ago) so bset guess is that the third party lenses with it probably have fairly advanced forms of stabilization.