SOLVED

Limit Query Results

Copper Contributor

I am looking for a way to stop users from select * from xxx in our database - 

Is there a way to set permissions or restrictions on certain tables to limit the result set to no more than 2500 records? 

2 Replies
best response confirmed by nkopczynski (Copper Contributor)
Solution

@nkopczynski , no, there is no build-in function to limit the result set for users (even because it makes no sense; which 2,500 records to return).

 

Create views or stored procedure returning a limited resultset (which don't make sense) and grant users permissions only on the views/SPs and not on the base tables.

@olafhelper Thanks for the reply - in most cases I agree with you it makes no sense, however I am in a situation where certain members of the "staff" non technical have elevated rights to the database.  They are writing bad queries = select * from "huge table" into #tmpTable - because they don't know how else to properly filter their queries.  This is a problem that I am trying to solve.  I have told the people in charge and they don't seem too concerned.  

 

The view idea is interesting - I might be able to create a view on top of those huge tables and lock down direct access to the table.  I will investigate that path.  Thanks

1 best response

Accepted Solutions
best response confirmed by nkopczynski (Copper Contributor)
Solution

@nkopczynski , no, there is no build-in function to limit the result set for users (even because it makes no sense; which 2,500 records to return).

 

Create views or stored procedure returning a limited resultset (which don't make sense) and grant users permissions only on the views/SPs and not on the base tables.

View solution in original post