How to implement LIMIT cluase for SQLServer at table level access?

Question

How to implement LIMIT cluase for SQLServer at table level access?

Anonymous

Hi I am trying to implement LIMIT clause at SQLServer database level. The objective is to make sure that no one has the full scan of the table, instead can only scan 10 records or so.

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-08-04T18:47:34.5233333+00:00

Rathi Dasgupta [C] Thank you for reaching out.

My understanding is that you are trying to use the LIMIT clause at the table level to restrict user access only the first 10 rows.

From my experience you can create a view as mentioned below by Olaf Helper then Grant users' access to that view.

Please don't forget to mark as accept answer if the reply was helpful. Otherwise let us know if you need more clarification.

Regards,

Oury

2 answers

Your answer

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-08-04T18:47:34.5233333+00:00

Rathi Dasgupta [C] Thank you for reaching out.

My understanding is that you are trying to use the LIMIT clause at the table level to restrict user access only the first 10 rows.

From my experience you can create a view as mentioned below by Olaf Helper then Grant users' access to that view.

Please don't forget to mark as accept answer if the reply was helpful. Otherwise let us know if you need more clarification.

Regards,

Oury

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

MySQL supports the LIMIT clause to select a limited number of records, while Transact-SQL for SQL Server and Azure SQL uses TOP.

Below code shows how SQL Server implements the same concept.

SELECT TOP 3 * FROM Customers;

And below code shows how MySQL implements the LIMIT clause to have a limit on the numbers of rows returned.

SELECT * FROM CustomersLIMIT 3;

Answer 2

Olaf Helper 47,436

LIMIT clause at SQLServer database level.

That's not possible, a result limitation is defined on query level and the user must define it on his own#

You can create views/stored procedure returning only a limited result set, then grant access for the users on that views/SP, but not on the base tables.

Share via

How to implement LIMIT cluase for SQLServer at table level access?

2 answers

Your answer