Holland will oversee all hockey operations with full autonomy, reporting directly to CEO Bob Nicholson.
The 63-year-old's first task will be finding a new head coach. He announced during his presser that Ken Hitchcock will not return behind the bench.
Holland added that he hopes to keep Keith Gretzky in the organization. Gretzky served as the club's interim GM after Peter Chiarelli was fired in January.
The Vernon, British Columbia, native captured three Stanley Cups and four Presidents' Trophies during his 22 years as Red Wings general manager. In total, he spent 36 years in the organization, initially serving as a scout before moving up the ranks to director of amateur scouting, assistant GM, and eventually, GM in 1997.
During his tenure, the Red Wings were known as one of the top drafting teams in the NHL. They most notably selected Pavel Datsyuk and Henrik Zetterberg in the sixth and seventh round in 1997 and 1998, respectively. Finding late-round gems was an integral part of Detroit's 25-season playoff streak.
Red Wings Governor Christopher Ilitch praised Holland for his contributions to the organization in a statement.
"Ken is a first-class person and a top-notch hockey man," said Ilitch.
Holland, who said Tuesday that he had envisioned finishing his career with the Red Wings, was replaced by Steve Yzerman in April. Despite losing the position, Holland initially agreed to stay on as senior vice president and signed a multi-year extension in Detroit before he was approached by Nicholson about the Oilers job.