How can I set Timeout by View, User or Role? For SQL 2008 R2, data views. 

Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query.

I have been unable to find timeout controls by View, User or Role using SSMS.

There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits).

I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this https://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client" 

Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application.


I have control over what is in the Data View Query, and any SQL settings that do not impact the main application.
I have control over the User Permissions that can access  the data view
I do not have control over external report application(s)
Snapshot isolation is not an option (application considerations)
The hints NOLOCK &amp; READPAST are not viable solutions in SQL 2008 R2 https://stackoverflow.com/questions/15770402/sql-2008-nolock-vs-readpast-considerations-for-reporting-accuracy
Given the constraints and in addition to query optimizations and tuning. 

Three stage approach, to limit potential conflicts when reporting from production server –


Use Resource Governor to limit report logins to Max 20% Min 0
Use an Operating Level Agreement (OLA) to require applications reporting from production databases to a 5 second timeout (DBPROP_COMMANDTIMEOUT)
Use a trace to track SQL queries against production database and confirm OLA compliance (filter by login)


Rationale: 


Resource Governor, does not have any contra-indications when limiting occasional report queries against OLTP with constant connect
OLA is not an iron clad guaranty, but if the timeout is used, it limits the potential exposure to the main application (OLTP)
Trace permits follow-up and validation of solution.


References 

Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx 

White paper  “Using the Resource Governor” from the Microsoft Download Center
http://msdn.microsoft.com/en-us/library/ee151608%28v=sql.100%29.aspx