CODEWITHSUNDEEP
sqlazureazure-sql-databasesql-grant
Jayendran
Jayendran

Reputation: 10920

How to grant Database scope credentials to users in Azure SQL Database

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.

Upvotes: 4

Views: 7760

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15608

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

Upvotes: 5

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

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.

Upvotes: 1

Related Questions