I noticed that all examples (that I've seen) of natively compiled stored procedures (in SQL Server) are defined using EXECUTE AS OWNER.

For example:

CREATE PROCEDURE [dbo].[InsertOrderXTP]  
    @id INT, 
    @date DATETIME2, 
    @status TINYINT  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS...

We started getting the following error while trying to execute natively compiled stored procedures we created with EXECUTE AS OWNER:

Could not obtain information about Windows NT group/user ...

This make sense because we had other un-related technical issue with the user that created those procedures.

Since we never used EXECUTE AS .. in regular ("interpreted") procedures, I tried removing it from the natively compiled stored procedure and it seems to to be working just fine.

But as I mentioned, it contrast from all the examples I've seen, and I couldn't find any relevant documentation that elaborates on this issue.

1. Must a natively compiled stored procedures be created with EXECUTE AS OWNER..."?
2. Is there a benefit to creating natively compiled stored procedures with EXECUTE AS OWNER... that doesn't exist in regular (interpreted) stored procedures?
3. Where can I find explicit documentation about this issue?

CodePudding user response：

This appears to be a historical limitation that is no longer required as of SQL Server 2016.

In the documentation for CREATE PROCEDURE, we have this note:

For natively compiled stored procedures, starting SQL Server 2016 (13.x) and in Azure SQL Database, there are no limitations on the EXECUTE AS clause. In SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.

Then on the separate page about EXECUTE AS there is this:

CALLER is the default for all modules except queues, and is the same as SQL Server 2005 (9.x) behavior.

Put those two things together, and we find that omitting the EXECUTE AS clause on a natively compiled stored procedure in SQL Server 2014 would have defaulted to an unsupported option. So all examples of natively compiled stored procedures which predate SQL Server 2016 would have needed an explicit EXECUTE AS clause to compile successfully.