Guy, Paul,

I have a way to do what you ask, but I don't like it! :)

First, in the stored proc, I begin by creating a temp table:

SELECT * INTO #TempTable FROM original_table_name_goes_here

Then, since the temp table belongs to the user, the search will be successful. At the end of the stored proc, I delete the temp table. You do have to allow INSERT INTO in the database options, though.

Since I haven't used that option very often, I can't say anything about performance, but I expect it to be slloooowwwwww...

---

>This is a good question.
>
>If, for security reasons, I do not want to give SELECT permissions to any of the SQL tables then I can just give access to Stored Procs and the stored procs can issue the SELECT.
>
>
>However, if I want to build an SQL statement inside a Stored Proc (based on paremeters and who the user is, I can easily build the SELECT string buut I have to issue a SQLEXEC call to execute the SELECT string. It is this SQLEXEC call that chokes if the user does not have SELECT rights on the table.
>
>So the big question is, how do you allow dynamic SQL statements to be constructed and executed without giving SELECT rights on the table itself?
>
>Guy
>
>
>
>>>Does anyone know how to pass a SQL search string into a stored procedure for execution. (Without string parsing!!). This means that table select permissions do not have to be given to individual tables.
>>>
>>>Thanks
>>>Paul
>>
>>Are you saying if you string execute a SQL statement in a stored procedure, that is considered 'selecting data?' ? Wouldn't the SP's need access to select data anyhow?
>>
>>BOb