Read-only user for Synapse Database

How-to setup read-only user for Synaps Database

-- Create a new user

CREATE LOGIN BIReadOnly WITH PASSWORD = 'set to something sensible';

-- Create a user in the target database

CREATE USER BIReadOnly FROM LOGIN BIReadOnly;

-- Replace 'your_schema' and 'your_table' with the actual schema and table names

GRANT SELECT ON SCHEMA::dbo TO BIReadOnly;

GRANT SELECT ON SCHEMA::rep TO BIReadOnly;

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[dynamics365_financeandoperations_opml_tst_sandbox] TO BIReadOnly

  NOTE: [dynamics365_financeandoperations_opml_tst_sandbox] is an external data source reference (but without "_EDS")

-- Create a new user

CREATE LOGIN BIAdmin WITH PASSWORD = 'set smth';

-- Create a user in the target database

CREATE USER BIAdmin FROM LOGIN BIAdmin;

-- assign it to role

ALTER ROLE db_owner ADD MEMBER BIAdmin;

---

User group read only access to Synapse Database

USE master

CREATE LOGIN [OPML_ProjectDataLakeView] FROM EXTERNAL PROVIDER;

USE opmltstdatalakesqlserverless

CREATE USER OPML_ProjectDataLakeView FROM LOGIN [OPML_ProjectDataLakeView];

USE opmltstdatalakesqlserverless

GRANT SELECT ON SCHEMA::rep TO OPML_ProjectDataLakeView

USE opmltstdatalakesqlserverless

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[dynamics365_financeandoperations_opml_tst_sandbox] TO OPML_ProjectDataLakeView