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.