A valid use of SELECT * occurs when introduced by EXISTS.
WHERE EXISTS (SELECT * FROM ...
Like all uses of SELECT *, this has the unwelcome (and unnecessary here) side-effect of preventing WITH SCHEMABINDING from being added if the code is inside e.g. a function.
Related Q & A: EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?
See also:
- Bad habits to kick : using SELECT * / omitting the column list by Aaron Bertrand
- When Select * Doesn’t Matter by Erik Darling.