A web article out of Brown University describes this relationship quite succinctly and well. I'll summarize the high points here and add in a few others from my own experience as an subject matter expert (SME). I'll also direct you to the article, titled Online Course Design for additional helpful information.

Defining the Roles

Faculty and SMEs have a mastery of the course content and are experts in their fields of study. They also have classroom experience that informs the course development experience. Instructional designers, on the other hand, Instructional Designers (IDs) have significant experience and knowledge of learning theory, online teaching methodologies, the LMS being used by the school, and a creative take on how to integrate content with technology to bring the course to life for distance learners.

The Relationship and The Work

The partnership begins when the SME and the ID develop an agreed upon list of learning objectives for the course and how these objectives flow in such a way as to scaffold the learning for students. Once the objectives are established, the SME and the ID set a schedule for when the content the SME will be writing will be delivered to the ID.

The ID keeps the SME on track timewise, helps keep the content inline with the agreed upon objectives, and ultimately takes the information and creates the class in the online environment including creative use of technology/multimedia (this work sometimes falls under the domain of another title "eLearning Designer." But the SME does not always communicate directly with the eLearning Designer).

What Happens Next

In many cases, especially when the ID and SME are contracted to work for a school or higher ed company, the completed course then belongs to the school. The same ID and/or SME (and maybe the third person - the eLearning Designer) may be called upon later to add updates to the course as continues to be used. In some, but not all, instances, the SME will also be asked to teach the course. This depends on the job description of the SME itself.