Executive privilege is raised when the president wants to protect information from disclosure. The most important Supreme Court case dealing with executive privilege is United States v. Nixon.
Archibald Cox, the special prosecutor investigating the Watergate scandal, subpoenaed tapes of Oval Office conversations between President Nixon and others about the scandal. Citing executive privilege, Nixon refused to turn over the tapes.
The Supreme Court found that absent a need to protect military, diplomatic, or sensitive national security secrets, the confidentiality of presidential communications is not significantly diminished by producing material for a criminal trial.
Simply put, presidents cannot refuse to cooperate with investigators or Congress. Whether courts uphold a claim of executive privilege depends heavily on the sensitivity of the information in question.