Are there these features please, on Azure SQL database service ?

Bamak Mrbi 31 Reputation points
2022-04-19T07:50:25.117+00:00

Hi, I'm from Bigquery, I would like to know if there are these features on Azure SQL Database ?
For a migration project

1) Authorized Views
-A view is created in a database (DWH layer or formatted)
-It points on tables from the RAW database (same server)
-Give access for the view to the underlying tables with a single click/SQL query (do not have to declare the view users on the underlying DB)
---> On the source database (sourcing dataset on the BQ side), the authorized view is declared "shared" <-- This is where it is practical & where I have a doubt of possibility on azure SQL server ?
Maybe a work around could be the instruction executed as at beginning of the view

2) Materialized views (sort of virtual tables based on underlying data <-- select ... from table A join table B .. group by

  • Greatly improves the performance of each read queries (power BI folding)
  • Recalculated when underlying data changes

3) View database users roles on management studio or azure data studio - directly on a window) without execute a query with metadata

Best regards and thanks a lot for your informations

Christophe

Azure SQL Database
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anurag Sharma 17,571 Reputation points
    2022-04-19T11:10:08.333+00:00

    Hi @Bamak Mrbi , welcome to Microsoft Q&A forum.

    Please check the below points for queries:

    1). Authorized view are basically used to query other tables based on certain permissions. In your case, it seems you want to query separate database on same server. This can be achieved using the Elastic Queries in Azure SQL. These queries will need 'ALTER ANY EXTERNAL DATA SOURCE' to be run. Once we have created the elastic queries, then these can be run using simple t-sql commands. Please refer to below article for more details:

    Get started with cross-database queries

    2). Materialized view are already part of azure SQL Database, it works exactly similar to how any views works on relational databases. These views query on the current data, so if the underlying data changes these views will return the latest result sets.

    CREATE VIEW (Transact-SQL)

    3). Through Management Studio or Azure Data Studio, we will not be able to see the users-role mapping, however we can still all the users and roles separately in SSMS. We need to use queries only to get the mapping between them:

    sys.database_role_members

    Please let us know if this helps or else we can discuss further on the same.

    ----------

    If answer is helpful please click on 194230-image.png as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!

  2. Bamak Mrbi 31 Reputation points
    2022-04-20T09:14:31.32+00:00

    Thanks a lot Anurag

    OK I see Authorized views feature doesn't exist for Azure Synapse & SQL database

    • It's a shame because it's super practical/usefull on BI (making layers / shared views layer for DATA Analysts )
    • It's probably a google patent
    • Actually GCP BQ customer "love" that !!!
    • Anurag I let relay to MS these kinds of shortcomings

    And also there is not a visual presentation (via azure data tool extensions or management studio) according to show database users roles ... for a least the DB owner

    Only run a T SQL script ... it's also a shame (my opinion)

    0 comments