2

Just want to find out if there is a way to set the "Remote Query Timeout" or "Execution Time out" for particular user, in SQL Server

Context:

our client wants access to a view in our database. therefore we are giving them their own connection username, but we want to ensure that the customer doesn't run expensive (long running) queries on our DB.

Adhil
  • 1,678
  • 3
  • 20
  • 31
  • 1
    Timeout and expensive queries are a different thing, in truth. A query can be "expensive" and run fairly quickly, and another can be inexpensive but take some time to run (perhaps due to a blocking process). Timeout, however, is generally set at the *connection* level, not the `LOGIN`/`USER` level; there isn't a `LOGIN` setting you can use to force a `LOGIN`'s connection to terminate after *x* seconds. – Thom A May 02 '23 at 08:22
  • 1
    Other possibilities do exist, although all require time and effort; a monitoring tool such as Redgate SQLMonitor could be configured to raise alerts for long-running queries using a specific object; you could set up a regular job to monitor current processes belonging to a specific login; you could create an AG replica of the database and allow the client access to the read-only replica database. – Stu May 02 '23 at 08:36
  • 2
    You can check out resource governor to see if it can be used to manage their resources. Or just give them a list of procedures with a fixed interface – siggemannen May 02 '23 at 09:59
  • 1
    Agreed, you probably want Resource Governor https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver16 – Charlieface May 02 '23 at 11:02
  • @siggermannen our SQL Server, is the standard edition, Resource Governor is only available on the enterprise edition, anything I can do on standard? – Adhil May 03 '23 at 06:41

0 Answers0