Requesting Assistance with Granting Read-only Permissions for Views in Azure SQL Serverless Pool to Guest User in a Canvas App

Question

Hello Azure Community,

I am currently working on a project that involves granting read-only permissions for views on the database level (SQL Serverless Pool) to a guest user in a Canvas app. However, I have encountered a challenge in determining the specific permissions that need to be assigned to a custom role.

So far, I have observed that assigning the "db_owner" role to a security group enables guest users to read data from both views and external tables in the Canvas app. On the other hand, granting the server role "sysadmin" to a guest user allows them to read data from an external table, but not from a view.

In order to overcome this issue, I have created a custom role and assigned an array of SELECT permissions for objects, databases, and tables. Unfortunately, I have not been successful in achieving the desired result.

I would greatly appreciate any guidance or suggestions on the correct set of permissions to be assigned to the custom role for enabling read-only access to views for guest users in a canvas app.

Thank you in advance for your help!

Best Regards,

Nicole

UPDATE:

I posted my solution below

Answer 1

To grant read-only access to views for guest users in a Canvas app, you can follow these steps:

1. Create a custom role specifically for granting read-only access to views.
2. Grant necessary permissions to the role: Grant the necessary permissions to the CustomReadOnlyRole for accessing the views. Use the GRANT SELECT statement to provide read-only access to the views.
3. Assign the role to the guest user: Once the role is created and permissions are granted, you need to assign the CustomReadOnlyRole to the guest user you want to provide read-only access to. Use the ALTER ROLE statement to assign the role to the user.

CREATE ROLE [CustomReadOnlyRole];

GRANT SELECT ON [schema].[view_name] TO [CustomReadOnlyRole];

ALTER ROLE [CustomReadOnlyRole] ADD MEMBER [guest_user];

Answer 2

Hello everyone,

I'm pleased to report that I've found a solution to the problem I posted earlier. The key is to create a custom database role in Azure, and then assign the role to a specific AAD Security Group or an individual AAD User (either a member or guest user). Here's the T-SQL script that outlines the process.

This script not only creates the role and assigns it to a user, but it also grants the role the necessary permissions to reference database scoped credentials and to select data from a specific schema. This allows the user to log in using AAD credentials and query views via a Canvas app. SQL Server then uses the associated credentials to authenticate to the external data source and retrieve the data.

I hope this helps anyone else encountering a similar issue. Don't hesitate to ask if you have any further questions or need clarification on any point.

Best regards,

Nicole

---

Remember to replace [DATABASE], [AAD SECURITY GROUP/ AAD USER(MEMBER OR GUEST USER)], [USER], [NEW USER], and [SCHEMA] with your actual values.

USE [DATABASE]

CREATE ROLE db_custom_reader;

GO

-- Create a user/group that is in the AAD
CREATE USER [AAD SECURITY GROUP/ AAD USER(MEMBER OR GUEST USER)] FROM EXTERNAL PROVIDER

GO 

-- This allows aad user to use the aad credentials to login to the database
CREATE LOGIN [USER] 
FROM EXTERNAL PROVIDER

GO

ALTER ROLE db_custom_reader ADD MEMBER [NEW USER]

GO

-- A user is granted the REFERENCES permission on the credential and the SELECT permission on the SCHEMA to
-- read the views/tables.
-- When the user queries the view via Canvas app, SQL Server uses the credential 
-- to authenticate to the external data source and fetch the data

GRANT REFERENCES   
    ON DATABASE SCOPED CREDENTIAL :: write_token  
    TO db_custom_reader

GO

GRANT SELECT ON SCHEMA::[SCHEMA] TO db_custom_reader;

GO

GRANT SELECT ON SCHEMA::[SCHEMA] TO db_custom_reader;