May 18, 2011 at 7:19 am
We have a user that is granted with EXECUTE permission on a Stored Procedure. The SP performs a SELECT that is created dynamiclly. When the SP is executed, it fails due to lack of SELECT permissions on the tables included in the dynamic select command. When running the SP without using the dynamic select command(i.e. explicitly running the SELECT and not the command created) - no problem occurs.
How can we circumvent this situation WITHOUT explicitly assign GRANT permission for SELECT to all the tables included in the SELECT command ?
Regards,
Eyal G.
Example :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[aaa] /* FAILS due to Select permission on unit table */
AS
DECLARE @actionQuery nvarchar(4000);
SELECT @actionQuery= 'SELECT * FROM unit'
EXEC ( @actionQuery)
====================================================
ALTER proc [dbo].[aaa] /* SUCSSEEDS */
AS
SELECT * FROM unit
May 18, 2011 at 7:34 am
Add an EXECUTE AS clause to the stored procedure (SQL Server 2005 and up) as part of the CREATE PROCEDURE statement. You probably want to execute as the owner.
The reason this breaks is because technically, the dynamic SQL executes in a separate context from the stored procedure. Therefore, security is immediately checked. If this causes ownership chaining to break, then setting the stored procedure up to execute as owner should solve this issue.
K. Brian Kelley
@kbriankelley
May 20, 2011 at 1:01 am
Brian,
Thanks for the reply.
Eyal
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply