SQL Server in-memory : Native compile with Execute as Owner

Question

SQL Server in-memory : Native compile with Execute as Owner

sakuraime 2,351

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/native-compilation-of-tables-and-stored-procedures?view=sql-server-ver15

May i know is it a must to mention Execute as Owner during the creation of native compile sp ? I can create without Execute as Owner .

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Erland Sommarskog 128.7K MVP Volunteer Moderator

A small correction to Dan and Tibor: In SQL 2014 EXECUTE AS OWNER was mandatory. They removed that requirement in SQL 2016. But this can explain why it still appears in some samples.

sakuraime 2,351 Reputation points

2020-10-24T08:32:42.827+00:00

thankyou .
but what's the different of having EXECUTE AS OWNER or not
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2020-10-24T08:51:49.91+00:00
EXECUTE AS OWNER means that inside the procedure you will assume the security context of the procedure owner, and thus have all the permission of that user. This as such is not unique to natively compiled stored procedures, but applies to regular procedures as well. The only thing that is special is that in SQL 2014 the default of EXECUTE AS CALLER was not permitted for natively compiled procedures.

Here is a quick demo to illustrate the difference.

CREATE DATABASE testdb ALTER AUTHORIZATION ON DATABASE::testdb TO sa go USE testdb go CREATE PROCEDURE one WITH EXECUTE AS CALLER AS SELECT SYSTEM_USER, original_login() go CREATE PROCEDURE two WITH EXECUTE AS OWNER AS SELECT SYSTEM_USER, original_login() go EXEC one EXEC two go USE tempdb DROP DATABASE testdb

Answer 2

Dan Guzman 9,411

The EXECUTE AS clause is not required for natively compiled stored procedures. The syntax diagram for natively compiled stored procedures in the CREATE PROCEDURE documentation page shows the clause in square brackets, indicating it is optional.

Answer 3

tibor_karaszi@hotmail.com 4,316

It is not a must. I can also create without specifying EXECUTE AS.

Share via

SQL Server in-memory : Native compile with Execute as Owner

2 additional answers

Your answer