You can certainly limit the query results. I'm doing it for StackQL using a stored procedure that looks something like this:
CREATE PROCEDURE [dbo].[WebQuery]
@QueryText nvarchar(1000)
AS
BEGIN
INSERT INTO QueryLogs(QueryText)
VALUES(@QueryText)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET QUERY_GOVERNOR_COST_LIMIT 15000
SET ROWCOUNT 500
Begin Try
exec (@QueryText)
End Try
Begin Catch
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_LINE() AS ErrorLine,
ERROR_STATE() AS ErrorState
End Catch
END
The important part here is the series of three SET statements after the log. This limits based on both the number of rows in the results and the expected costs of the query. The rowcount and query governor values can be use variables, and so it shouldn't be hard to modify that to change the restriction based on the current user as well.
However, you should also note that it's pretty easy for users who are "in the know" to bust out of that if they want. In my case I consider the ability to get past the limits from time to time a feature. But it's also why I do the logging: the code to get past the limits sticks out in the logs, and so I can easily catch and ban anyone doing it too often without my permission.
Finally, any user that calls this should be only in the denydatawriters role, the datareaders role, and then given explicit permissions to execute just this stored procedure. Then they can't really do anything but select on existing tables.
Now, I'll anticipate your next question is whether you can make this automatic from somewhere like report builder or management studio. Unfortunatley, I don't think that's possible. You'll need to give them some kind of interface that makes it easy to call your stored procedure. But I could be wrong here.