Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain number of cycles - and then the query times out - i.e. preventing this user from really using up any significant resources per query?
Anyone know of a way to do this?
thank you!
There are ways, but nothing which is perfect.
If the user only accesses the SQL Server database through a data layer (e.g., the user only goes through a specific web application and does not connect using SQL Server Management Studio), George Mavritsakis's answer can work. If they do connect via SSMS, however, you have to go a step further.
You could set the query governor cost limit (in sp_configure) to a particular time limit, but that affects all users by default. This will kill a query if the expected time is greater than your maximum, noting that expected time depends on statistics and more complex queries can foul up your calculations.
If you just want to do this for one login, you can create a logon trigger and if that login is your resource-wasting user, run
SET QUERY_GOVERNOR_COST_LIMIT 600 --or whatever value you want
in the trigger body. Note again that the governor uses estimated times rather than actual times, so it's not foolproof. It would, however, stop an attempt to do a select * against a 10 million row table if that's the actual problem you're having.
The downside of creating a logon trigger is that you have to remember that it's there. Like all other types of triggers, it could lead to "unexpected" behavior if somebody who is not aware of it (e.g., your eventual replacement) tries to dig through why this person keeps getting weird error messages.
Note that in more recent versions, Microsoft has pushed away from the query governor, especially as they've shifted to the resource governor. But it's still in 2012 (and doesn't appear to be deprecated) and if you're stuck on 2005, I guess that's no problem...
query_governor_cost_limit in behalf of an user in a logon trigger (actually, you can, but the setting works during the life of the trigger, not the user session as one may think). Also, it is important to note that query governor cost limit is an advanced option, so first you should run exec sp_configure 'show advanced options', 1 to be able to change this option.
Commented
Apr 22, 2018 at 23:48
I think you should controll that from the DAL (Data Access Layer) point of view. Set timeouts there. For example, for ADO.NET you could set a timeout at the Command, different for each user.
