3

I've Azure SQL Database, where we will bulk load the CSV files from azure blob to SQL table.

So far we can easily able to do that with the admin credentials.

The below query is working under admin credentials but not working for normal user credentials

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'WoZ0ZnpXzvdAKoCPRrsa7Rniq7SNjAcZxL..............';

I'm getting an error as

User does not have permission to perform this action.

So I've tried to grant the access for the user from here

GRANT CREATE ON DATABASE SCOPED CREDENTIAL::AZUREBLOBSTORAGECREDENTIALPERMISSION TO MYUSER

The above grant has some syntax error like below.

Incorrect syntax near DATABASE.

Jayendran
  • 9,638
  • 8
  • 60
  • 103

2 Answers2

3

Looking at the official documentation the statement GRANT permission ON DATABASE SCOPED CREDENTIAL does not support CREATE as a possible permission. The permissions this statement supports are: CONTROL, TAKE OWNERSHIP, ALTER, REFERENCES AND VIEW DEFINITION. Probably the following statement may work for you:

GRANT CONTROL ON DATABASE SCOPED CREDENTIAL::AZUREBLOBSTORAGECREDENTIALPERMISSION TO MYUSER
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
1

From the docs, it requires CONTROL permission on the database.

USE AdventureWorks2012;  
GRANT CONTROL ON DATABASE::AdventureWorks2012 TO Sarah;  
GO

See this link for further detail.

Jayendran
  • 9,638
  • 8
  • 60
  • 103
Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
  • This will grant overall control to the `myuser`, but I specifically need to grant the permission for only the **DATABASE SCOPED CREDENTIAL** Don't we have any choice for this? – Jayendran Aug 14 '18 at 09:22
  • Unfortunately the security grain you are after is not possible, you need to grant CONTROL as per the docs. You need to create the scoped credential using a high priv account and then secure the access to the credential using more granular permissions. https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-scoped-credential-transact-sql?view=sql-server-2017 – Murray Foxcroft Aug 14 '18 at 09:28