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!

| improve this question | | | | |
  • When you say "user," do you mean controlling resources for a given database user, or for a server login? – Jon Seigel Jul 30 '12 at 17:30
  • for a given database user – Faisal Vali Jul 30 '12 at 17:38
  • Do you mean by CONNECTION? – JNK Jul 30 '12 at 19:35
  • i was thinking of any connection using that db login - but if you have a method for limiting it based on ip from which the query is coming - that would be useful too - – Faisal Vali Jul 30 '12 at 20:13

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...

| improve this answer | | | | |
  • 1
    You can't set 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. – Diego Queiroz Apr 22 '18 at 23:48

I am afraid it is not possible to set the limit in the logon trigger. Using SET in the logon trigger is futile as the SET command works only in context of the trigger (ie from the command to the END statement). As soon as the session finishes executing the trigger, the value is reset to its original value.

| improve this answer | | | | |

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.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.