Intercept and Rewrite Queries in SQL Server 2005
sql-server, sql-server-2005
Solution
You could use this approach. It works like a charm for me:)
Rather than attempting to intercept and modify SQL calls originating from the application, perhaps you can instead implement an abstraction layer without changing the application's SQL. For example, if you can modify the DSN or login connection string for the application, then connsider the following. Let's assume the current database is [A]. Create a new database [B] that contains views and functions (but not tables) with the same name as what is in [A], then modify them to reference the tables in [A]. Add whatever additional joins, filtering, etc. are needed to implement your (what I'm assuming) row based security. Then, modify the application DSN to use database [B] instead of [A].
link
Problem
We have an application built on top of SQL server 2005 that we have no control over. We've recently discovered that this application is sending some very inefficient SELECT queries to SQL that is causing serious capacity issues on the database. I know the right solution is to crack open the code for the application and change the queries, but for reasons I won't go into that's going to take a very long time. Is there some way we can intercept this specific query on SQL server and selectively re-write it to something more optimal?