SQL Cross-Schema stored procedure permission issues

Given a simplified stored procedure

CREATE OR ALTER PROCEDURE [FooSchema].[Foo]
AS
SELECT
    B.*,
    FROM [BarSchema].[Bar] AS B
    WHERE [...]

After granting EXEC to a user on the FooSchema, but not on the BarSchema, this stored procedure will fail with the message

The SELECT permission was denied on the object 'Bar',

Is there a way to make the user access [FooSchema].[Foo] without exposing [BarSchema].[Bar]’s entire data set as the stored procedure already filters out data relevant to this user.

Answer

Easiest option is to use [WITH EXECUTE AS] statement, where the username specified has the exact permissions needed for the objects in question.

CREATE OR ALTER PROCEDURE [FooSchema].[Foo]
WITH EXECUTE AS '<username>'
AS
SELECT
    B.*,
    FROM [BarSchema].[Bar] AS B
    WHERE [...]

Advertisement

Answer