How can we improve Microsoft Azure SQL Data Warehouse ?

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.

23 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Brent shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Dave Mulanaphy commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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 ...

Feedback and Knowledge Base