SQL 2000 Sproc Role security bypassed when using QBF?



Hi,

Platform: SQL Server 2000 SP4, VB.NET

I've got a Query By Form (QBF) sproc that is used in a search process by one
of our internal business apps.
All sprocs for this application have access controlled by membership to a
custom Role.
Membership to the Role is via a fixed SQL user account, which is part of the
application's connection string (I'm using the MS-P&P Enterprise Library
DAAB mechanisms).

The QBF code suits the application's search requirements nicely, but I am
concerned over the additional security permissions it requires beyond a
normal SPROC.

Essentially, the QBF syntax (basically, a query built dynamically as a
varchar in the sproc, according to variable input parameters) seems to cause
the SPROC to ignore the normal permission mechanism. It requires me to set
a SELECT permission for the USER instead of just EXECUTE on the sproc.

Does anyone have some guidance/tips they could provide me with about this
aspect?
E.g.
- Is this approach deprecated in favour of a better technique?
- Is it bad practice to use this approach?

I'd prefer not to have to set the additional SELECT permission directly on a
set of tables if I can avoid it.

Thanks.

Al


.