Hi All,

SQL 2000 - scenario:

A User - "Bob" does not have any rights to the Customer table.
There is a SP "GetCustomer" that does a simple "Select * from Customer" - Bob has execute rights to this SP.
Bob runs GetCustomers - no problem - the customers come back.

NOW

GetCustomer is rewritten to do the same thing, but via Dynamic SQL - ie. Execute('Select * from customer')
AND
Bob runs GetCustomer and gets a permission error that he doens't have rights to the customer table - very frustrating.

I need to keep the security of Bob NOT having direct rights to the Customer table, but also need Dynamic SQL. Advice?

Thanks,