asked
how is it possible user able to execute inside sp who doesn't have execute permission?
I have two sps as see below SP 1 CODE: create PROCEDURE [dbo].[SP1] ( @p_empid int ) AS begin select emp_id,emp_sal from dbname.dbo.emptest where emp_id=@p_empid end SP 2 CODE: create proc dbo.[SP2] ( @p_empid int ) as begin exec [SP1] @p_empid end Explanation: Second SP SP2 using first SP SP1 execution But My doubt is User have SP2 execute permission but doesn't have SP1 execute permission When I see user execute SP1 he get error as Msg 229, Level 14, State 5, Procedure SP1, Line 1 EXECUTE permission denied on object 'SP1', database 'RM', schema 'dbo' But When user Execute SP2 He is able to execute SP2 without any error( i.e User doesnt have SP1 permission but user have only SP2 execute permission) How is it possible? Can any one explain to me? Note: User have doesnt have execute permission in SP1 but User have execute permission in SP2 SP2 contains SP1 but how wonder User have able to run and execute SP2 without any error.
If the owner of SP2, which the user in question has permission to execute, has permission to execute SP1, then the user, by extension, gets that permission, but only through the execution of SP2. So, if you don't want the user to execute SP2, you have to explicitly revoke that privilege, or they'll be able to execute SP1 through SP2. Alternatively, you can make SP2 owned by some other user that doesn't have access to SP1. But then... execution of SP1 through SP2 is not going to work correctly.
15 People are following this question.
