SQL Server - Intercept SQL Server Queries

Asked By zac on 21-Aug-08 11:10 PM
Is there any way in SQL Server 2005 to intercept and manipulate
queries before the server executes them? I'm working on a complicated
application with non-trivial security rules that are implemented via
table-valued functions that take parameters only known at runtime. The
application uses a reporting tool with a data access pattern which we
cannot modify. These are not just stored procedures so we cannot
intercept them by simply replacing the proc. What I need is a way to
filter and mutate incoming queries either at the SQL-server level, or
at the client data-access provider level. This seems like a reasonably
simple feature to have on both ends (client and server). All it would
need to do is pass in the query as input and possibly some other
contextual information, and let me write and return the output string,
that's it.

Does SQL Server provide hooks for this? Also, can anyone provide any
information as to how the SQL profiler works?




JohnBar replied on 21-Aug-08 06:32 PM
In SQL server 2005 you can create triggers at the server level. I am not sure
100% if this will help you or not, but I have seen articles where they
capture more detailed logging regarding users connecting to the server using
triggers.

its in books online, you can use CLR...etc. As I said though, I am not 100%
sure it will meet your need or can.

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
EricRussel replied on 22-Aug-08 03:56 PM
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].