If you are using Synapse Analytics with SQL Pool Serverless (former SQL Pool On demand) and you want to manage a notion of “principle of least privilege”, there are a few things to take into account.
The creation of a USER using for example an AD group:
CREATE USER [MyAdGroup] FROM EXTERNAL PROVIDER
With [MyAdGroup], a group in the Active Directory to which we want to give the minimum amount of rights in order to execute a query on our Synapse SQL Pool Serverless.
DATABASE SCOPED CREDENTIAL access
Indeed, if you define your access to storage accounts via a Shared Access Signature, you will need to create DATABASE SCOPED CREDENTIAL.
In this case, you will most likely have used the following syntax to create your data source:
CREATE MASTER KEY CREATE DATABASE SCOPED CREDENTIAL [SasToken_MyStorage] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '?sv=2019-12-12&ss=bfqt&srt=sco&sp=rdlx&se=2099-09-02T21:03:45Z&st=2020-08-31T13:03:45Z&spr=https&sig=z11sZ9cHpPRf0MxxxxG8J9c%3D'; GO CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH ( LOCATION = 'https://monstorage.blob.core.windows.net/insights-logs-pipelineruns/resourceId=/SUBSCRIPTIONS/', CREDENTIAL = [SasToken_MyStorage] )
To give rights to users on the Scoped Credential Database, you must therefore use:
GRANT CONTROL ON DATABASE SCOPED CREDENTIAL :: [SasToken_MonStorage] TO [MyAdGroup]
The view access
GRANT SELECT ON Object::[dbo].[Myvue]
That would be enough if you didn’t use something like:
SELECT * FROM OPENROWSET( BULK '/folder/*.parquet', DATA_SOURCE = [MaDataSource], FORMAT = 'PARQUET' ) AS file
Because at this step, an error message appears:
You do not have permission to use the bulk load statement
ADMINISTER DATABASE BULK access
GRANT ADMINISTER DATABASE BULK OPERATIONS TO [MyAdGroup]
Note that if you used an SQL Login you would not have the problem because it would be a member of the public role server level and would therefore have the right to use BULK …
Use a Login FROM EXTERNAL PROVIDER
As we have seen previously, using a Login solves the problem, it is therefore possible to create our User via a Login to have direct rights to BULK operations.
CREATE LOGIN [MyAdGroup] FROM EXTERNAL PROVIDER CREATE USER [MyAdGroup] FROM LOGIN [MyAdGroup]