SUMMARY
The "Implicit Cursor Conversions" topic in SQL Server Books Online references a table that details the steps that SQL Server takes when the requested cursor type cannot be satisfied.
In step 8, the "Conversions triggered by" column states that a covered index must be defined on the ORDER BY clause of the query to avoid conversion from a dynamic to a keyset cursor. The detailed explanation in the
Conversion Triggered By cell states:
Query generates an internal work table, for example the columns of an ORDER BY are not covered by an index.
This implies that you must have a covered index to avoid converting to a keyset cursor. In this context, a covered index is one that contains all of the columns referenced on the ORDER BY clause.
This is incorrect. So long as the query processor can find a plan that satisfies the dynamic cursor, implicit conversion will not take place to a keyset.