Support EXECUTE AS for stored procedures
As part of the ELT procedure the user staging data should be able to call a transform procedure to ingest the data without having read/write permissions to the production tables.
Thanks for your feedback. Azure SQL Data Warehouse Support for EXECUTE AS feature is now in progress.
When this feature will be available
Dave Mulanaphy commented
This would be very useful for us to be able to call one stored procedure from another but specify a different sql login. We would use this to execute a child stored procedure from a parent but execute as a different user who would be mapped to a different resource class than the user executing a child stored procedure.
In some of our stored procs that do ELT, we call a separate stored proc to log out useful information. I would like to call the logging proc as a default or small resource class user when the parent stored proc is executed as a medium or large resource class.
Azure DBA commented
We have the same requirement where a design team specifies the DDL (i.e., column/table names, data types, nullability), the database admin team performs the phyiscal implementation (i.e., chooses distribution, indexing) and a third team (ETL) owns the ongoing production loading of the tables.
It is imperative that one user can define procedures that allow other users to run said procedures in a context where the calling user is known, but the imbedded SQL runs as another user (typically with elevated permission).
Postgres and Greenplum provide this mechanism with SECURITY DEFINER specification on UDFs. Teradata provides this mechanism via their macro and access rights implementation. I believe other versions of SQL server also provide exactly "EXECUTE AS".
Dean Pachosa commented
A colleague and I spent 1 hour trying to setup permissions on all the objects that were touched by a stored procedure. Each time we hit an issue, he would add that permission and then we would re-run. painful ...