Permissions define the level of access for the operations that you can perform in Microsoft Azure Synapse SQL.
You must verify the following permissions:
•Ensure that a default schema is present at the account level or user or group level in Microsoft Azure SQL Data Warehouse.
•Verify that either the db_owner privilege or the following more granular privileges are granted to the user to connect to Microsoft Azure SQL Data Warehouse and perform operations successfully:
- EXEC sp_addrolemember 'db_datareader', '<user>'; // Alternately assign permission to the individual table.
- EXEC sp_addrolemember 'db_datawriter', '<user>'; // Alternately assign permission to the individual table.
- GRANT ALTER ANY EXTERNAL DATA SOURCE TO <user>;
- GRANT ALTER ANY EXTERNAL FILE FORMAT TO <user>;
- GRANT CONTROL TO <user>; // To grant all permissions on the database.
or
GRANT ALTER ANY SCHEMA TO <user>;// To grant permissions only on the schema.
- GRANT CREATE TABLE TO <user>;
- Assign required privileges for tasks performed through Pre-SQL and Post-SQL commands.
•If you configure the staging schema name in the connection properties, ensure the following additional privileges are granted to the user:
- ALTER ROLE db_datareader ADD MEMBER <user>;
- GRANT ALTER ANY EXTERNAL DATA SOURCE TO <user>;
- GRANT ALTER ANY EXTERNAL FILE FORMAT TO <user>;
- GRANT CREATE TABLE TO <user>;
- GRANT ALTER ON SCHEMA::<staging_schema_name> TO <user>;// Ensures this user does not have access to create or drop an external table in another schema.
- GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::<db_credential_name> TO <user>;// Ensures this user does not have access to drop the credentials.
For example, GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::db_creds1 TO srvls;
•If you have the ALTER ANY SCHEMA permissions, you must create the Master Key, Database Scoped Credential, and External Data Source in Microsoft Azure Synapse SQL that require the CONTROL permission on the database and specify the external data source when you create a connection.
Also, Microsoft Azure Synapse SQL Connector does not delete the Database Scoped Credential and External Data Source. You must manually delete the Database Scoped Credential and External Data Source.