Stored Procedure - Dynamic Select

From: Eric Fleet (anonymous_at_discussions.microsoft.com)
Date: 05/14/04 

Date: Fri, 14 May 2004 06:46:04 -0700

I'm working in an environment where I am using stored procedures as the application's interfact to the database. The users have no direct rights to the underlying tables. Now I am tring to create a dynamic query. Ofcourse, this doesn't work because of the right issue. The query should return 1 or more columns based on the input (up to a couple dozen columns), and the where statement is also dynamic.

I've considered setting up a view, and giving 'select' access to the proper roles, but this just doesn't feel right...

Any thoughts?

Eric