is there a way to limit the amount of rows a user can query?

Question

Post reply

is there a way to limit the amount of rows a user can query?

SQLSandwiches

SSCrazy

Points: 2143
More actions
February 3, 2010 at 3:12 pm

#215229

Is there anyway to limit the amount of rows a user can return on a query?
For example, I want to give a junior employee access to the DB but only want his user to return 1000 rows. There have been times when a inexperienced user has done a select * from a huge table and really slowed things down.
I looked into resource governor but I only saw you could limit CPU usage.
Is there some way to create a schema for that user that would do this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could create a role for that user (and any other user you'd like to add).

Create views or stored procedures or functions for those user and lock down all tables.

Within those views or sp's you could use a top(1000) clause or, when using a sp or a function, you could even use a parameter to define how many rows would be returned.

Something like:

DECLARE @n INT

SET @n=10

SELECT TOP (CASE WHEN @n>4 THEN 1 ELSE @n END) *

FROM TABLE

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

If you give users full access to a database eventually they will write the query from hell. Find an alternative solution and get rid of select *.

The only way you can achieve this is to force a set rowcount 1000 on the session. You can't use TOP unless you also force every query to use an order by as the actual order of data returned by a top won't always match the first x rows where you don't use a TOP.

I admit I have no idea if you can set rowcount as session level or how - but essentially that's your only route, using top would force you to explain to users they must always use an order by to get the data they want.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 3

write a stored proc that pulls from sysprocesses and logs the spids running. If the spid runs to long execute the kill command. You could run this stored proc every min in a sql agent job. If you also drop spids from your tracking table that are no longer active you have your clean up too. you can also use this type of script to tracking blocking users and act on it. I would attempt to email some one or at least track if the KILL cmd was executed and what was executed (should be tracked in the sql log also) - this is a better idea than limiting rows. Even the best user or developer can write a bad script and cause issues.