You pretty much said it yourself. The user needs to have direct access to the table if you make the select in dynamic sql. Try this to solve your problem :

Declare @maxrows as int

set @maxrows = 10

Set rowcount @maxrows

select * from dbo.SysObjects order by name

set rowcount 0

This will allow you to use a select in a static proc instead of dynamic sql.