Oct 1, 2013

EXECUTE AS clause is required, and EXECUTE AS CALLER is not supported, with natively compiled stored procedures

If we will create a very simple natively complied stored procedure in the sql server 2014:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING
AS
     SELECT 'Simple Procedure'

We may get following error message:

Msg 41320, Level 16, State 1, Procedure uspGetEmployee, Line 16
EXECUTE AS clause is required, and EXECUTE AS CALLER is not supported, with natively compiled stored procedures.

Cause: By default in sql server, stored procedures are executed as caller and we cannot execute natively complied stored procedure as caller.

Solution: Execute natively compiled stored procedure as OWNER, user or SELF. For example:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
    
     SELECT 'Simple Procedure'

END


No comments:

Post a Comment