Native compiled stored procedure

• N_Muller

  SSCarpal Tunnel

  Points: 4037

  More actions

  February 8, 2017 at 10:10 am

  #334893

  I'm new to native compiled stored procedure and I'm getting an error during a simple store procedure creation. I hope someone can help me.  Thanks.

  The error I'm getting is:

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

  The code is:

  create table ASP_MO
  (
  Id int not null index ix_ASP_MO HASH ( Id ) WITH ( BUCKET_COUNT = 512),
  Name varchar(20),
  Description varchar(50)
  ) with (memory_optimized = on, DURABILITY = SCHEMA_ONLY)
  go

  create procedure ASPInsert_MO
  with native_compilation, schemabinding
  as 
  begin atomic with
  (
  transaction isolation level = snapshot,
  language = N'English'
  )
  insert ASP_MO ( Id, Name, Description )
  values ( 10, 'abc', 'xyz' )
  end
  go

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  February 8, 2017 at 10:32 am

  #1927379

  N_Muller - Wednesday, February 8, 2017 10:10 AM

  I'm new to native compiled stored procedure and I'm getting an error during a simple store procedure creation. I hope someone can help me.  Thanks.

  The error I'm getting is:

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

  The code is:

  create table ASP_MO
  (
  Id int not null index ix_ASP_MO HASH ( Id ) WITH ( BUCKET_COUNT = 512),
  Name varchar(20),
  Description varchar(50)
  ) with (memory_optimized = on, DURABILITY = SCHEMA_ONLY)
  go

  create procedure ASPInsert_MO
  with native_compilation, schemabinding
  as 
  begin atomic with
  (
  transaction isolation level = snapshot,
  language = N'English'
  )
  insert ASP_MO ( Id, Name, Description )
  values ( 10, 'abc', 'xyz' )
  end
  go

  It's pretty much just as the error is telling you. Execute As is required for native compilation. And you can't use Execute As Caller. So you can use Owner, User or Self in the Execute As. By default, stored procedures with execute as caller - the account executing the stored procedure. When you use the default, permission checks are done at runtime.
  If you use one of the other options, permission checks can be done when the stored procedure is created. 
  So if you were to use Execute As Owner, you would create the stored procedure with:
  create procedure ASPInsert_MO
with native_compilation, schemabinding, execute as owner
as
  The different options and the contexts for Execute As are listed in this documentation:
  EXECUTE AS Clause (Transact-SQL)

  Sue