Again - there are no simple "Select * from table" queries actually -there - just illustrating that, with a user having access to a stored proc but not a table, the following occurs:

Create Procedure MyProc as Select * from Table - Works Fine when they execute MyProc

whereas

Create Procedure myProc as execute("select * from Table") - throws a permissions exception

While this is not the exact scenario - whatever security logic is at work to make this happen this way is the same thing that is preventing the more complex real scenario fail. I like to break things down to their simplest components to ask questions. Much more effective than lots of detail that is not needed to resolve the issue (if it is resolvable)

Thanks,



>Ken,
>
>Why would you not have want to give that detail? It wasn't much, and it would have saved some responses that could not work given your requirements. Given your requirements, the only thing I can think of off-hand would be to encrypt all the data in the data base and have the decryption algorhythms only in the application. But, what I don't get is if you can make it work with a query like SELECT * FROM Customers in a proc that the user has access to execute (even though the user can't read the table), then what's different about the query that is failing? Is it something unique to certain queries that is causing you trouble?
>
>Chad
>
>>I had hoped not to have to explain the whole situation, but here is a summary of the requirements.
>>
>>1) No access to any tables except through our application
>>2) No hard-named users or roles (no super user accounts)
>>3) no user names or passwords can be embedded in application or stored on client machine in any way - even encrypted
>>4) Both Win and SQL authentication must be available
>>
>>that's most of it. We "solved" it with a single stored procedure that took session ID's and then passed all SQL requests on to sp_executeSQL - worked great until we started pulling permissions back and ran into the problem as described.