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,
Ken B. Matson
GCom2 Solutions