Ensure that a default schema is present at the account level or user or group level in

Microsoft Azure Synapse SQL

.

Verify that either the

db_owner

privilege or the following more granular privileges are granted to the user to connect to

Microsoft Azure Synapse SQL

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.

When you use managed identity authentication to connect to Microsoft Azure Synapse SQL, grant permissions to the virtual machine and user identity. For example,

GRANT CONTROL TO <virtual machine name>;

and

GRANT CONTROL TO <user identity name>;