A member of my team was doing some performance test and tweaks on SQL stored procedures... and noted that;
within the same stored procedure container with the same exec permissions on that sp;

case 1 : calling a sql query string (select) statement with variables, joins, etc...

case 2 : building the sql query string and calling sp_executesql with the sql and all the parameters passed into it

In testing, case 1 was dramatically slower than case 2 . Makes sense, all good, because the caching is what we were after in that regards.

What surfaced though is that granting permissions to a user to run the stored procedure, in case 1 , meant they didn't need explicit permission to all the tables included in the joined select statement, while in case 2 with calling the query through sp_executesql we would have to explicitely grant permission to all the tables in the sql query for it to run successfully.

Questions//

First is why... is there a specific rationale as to why the permissions are implied and cascaded with one, and not the other, and

Second is... is there a work around***?

Both cases were executed within the same stored procedure container with no changes to permissions. case 1 works fine, case 2 triggers errors with no permissions to all the tables.

case 1 sample code

ts.tick_serv_id AS tick_serv_id
       ,ts.ticket_id AS ticket_id    
       ,ts.service_id AS service_id
       ,ts.quantity AS quantity
       ,ts.employee_id AS employee_id    
       ,ts.commission AS commission
       ,ts.assumed_service_date AS assumed_service_date
       ,ts.service_date AS service_date
       ,ts.status AS status
       ,ts.comments AS comments
       ,s.service_type_id AS service_type_id    
       ,st.description      AS description
       ,st.edit_quantity AS edit_quantity
       ,s.print_text AS print_text
       ,sr.unit_type AS unit_type
       ,sr.rate AS rate
       FROM ticket_services ts
   INNER JOIN tickets t ON t.ticket_id=ts.ticket_id
   INNER JOIN services s ON ts.service_id=s.service_id                                   
   INNER JOIN service_rates sr ON ts.service_id=sr.service_id 
   INNER JOIN service_types st ON s.service_type_id=st.service_type_id     
   WHERE (tick_serv_id = @tick_serv_id OR @tick_serv_id IS NULL)
     AND (ts.ticket_id = @ticket_id OR @ticket_id IS NULL)
       AND (ts.service_id = @service_id OR @service_id IS NULL)
       AND (ts.quantity = @quantity OR @quantity IS NULL)
       AND (ts.employee_id = @employee_id OR @employee_id IS NULL)
       AND (ts.commission = @commission OR @commission IS NULL)
       AND (ts.assumed_service_date = @assumed_service_date OR @assumed_service_date IS NULL)
       AND (ts.service_date = @service_date OR @service_date IS NULL)
       AND (t.open_date BETWEEN sr.open_date AND sr.close_date)
       AND (ts.status = @status OR @status = '')
       AND (ts.comments = @comments OR @comments = '')

case 2 sample code

@tick_serv_id       bigint,
      @ticket_id            bigint,
      @service_id          int,
      @quantity            int,
      @employee_id         int,
      @commission          float,
      @assumed_service_date datetime,
      @service_date        datetime,
      @status              char(1),
      @comments            varchar(500)'

DECLARE @sql nvarchar(max) = N'
              SELECT
              ts.tick_serv_id 
              ,ts.ticket_id  
              ,ts.service_id  
              ,ts.quantity  
              ,ts.employee_id  
              ,ts.commission 
              ,ts.assumed_service_date 
              ,ts.service_date  
              ,ts.status  
              ,ts.comments 
              ,s.service_type_id  
              ,st.description      
              ,st.edit_quantity  
              ,s.print_text AS print_text
              ,sr.unit_type AS unit_type
              ,sr.rate AS rate
      FROM ticket_services ts
         INNER JOIN tickets t ON t.ticket_id=ts.ticket_id
      INNER JOIN services s ON ts.service_id=s.service_id                                       
      INNER JOIN service_rates sr ON ts.service_id=sr.service_id     
      INNER JOIN service_types st ON s.service_type_id=st.service_type_id  
         WHERE 1 = 1 AND (t.open_date BETWEEN sr.open_date AND sr.close_date)'
      + CASE WHEN @tick_serv_id IS NOT NULL THEN
        N' AND tick_serv_id = @tick_serv_id' ELSE N'' END
      + CASE WHEN @ticket_id IS NOT NULL THEN
        N' AND ts.ticket_id = @ticket_id' ELSE N'' END
      + CASE WHEN @service_id IS NOT NULL THEN
        N' AND ts.service_id LIKE @service_id' ELSE N'' END
      + CASE WHEN @quantity IS NOT NULL THEN
        N' AND ts.quantity = @quantity' ELSE N'' END
      + CASE WHEN @employee_id IS NOT NULL THEN
        N' AND ts.emplyee_id = @employee_id' ELSE N'' END
      + CASE WHEN @commission IS NOT NULL THEN
        N' AND ts.commission = @commission' ELSE N'' END
      + CASE WHEN @assumed_service_date IS NOT NULL THEN
        N' AND ts.assumed_service_date = @assumed_service_date' ELSE N'' END
      + CASE WHEN @service_date IS NOT NULL THEN
        N' AND ts.service_date = @service_date' ELSE N'' END
      + CASE WHEN @status <> '' THEN
        N' AND ts.status = @status' ELSE N'' END
      + CASE WHEN @comments  <> '' THEN
        N' AND comments = @comments' ELSE N'' END
         ;

              EXEC sys.sp_executesql @sql, @params,
              @tick_serv_id                                                
              ,@ticket_id                               
              ,@service_id                               
              ,@quantity                                                       
              ,@employee_id                                                 
              ,@commission                                             
              ,@assumed_service_date                    
              ,@service_date                            
              ,@status                                  
              ,@comments;