How do I create a user for SQL Server whose queries (any/all) are time limited?

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

StackExchange Database Administrators Q#21694, answer score: 3