I have a stored procedure that selects a field(ufilter) from a table. The ufilter field contains a clause to use in a where statement to filter the records returned to the caller. Example of contents of ufilter: make='Oldsmobile' . The sp then should read the content of the cars table, based on the the dymanically generated select statement
I grant Execute rights on the spGetCars stored procedure. Runs fine when I execute it from my account. However, when I use a less privileged account, tells me that select is not granted on Cars table.
I grant execute rights to the spGetCars for a less privilieged windows logon. As the Cars table has the same owner (dbo) as the stored procedure, shouldn't the permission chain allow selecting from the Cars table without explicitly granting the select rights on the Cars table to the less privilieged windows logon account?
Thank you for any assitance you can provide.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER proc [dbo].[spGetCars]
set nocount on
DECLARE @ufilter varchar(200)
DECLARE @uexpdate datetime
DECLARE @cmd varchar(200)
select @ufilter=ufilter, @uexpdate=uexpdate from UFILTERS where uid=@uid
IF ISNULL(@ufilter, 'ZZTOP') = 'ZZTOP'
select * from Cars where 1 = 2
IF @ufilter = 'NONE'
select * from Cars order by Make, Model
set @cmd = 'select * from Cars where ' + @ufilter