Forum Discussion

nkopczynski's avatar
nkopczynski
Copper Contributor
Oct 02, 2023

Limit Query Results

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? 

  • olafhelper's avatar
    olafhelper
    Oct 04, 2023

    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's avatar
    olafhelper
    Bronze Contributor
    Oct 04, 2023

    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.

    • nkopczynski's avatar
      nkopczynski
      Copper Contributor
      Oct 04, 2023

      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

Resources