Unable to create a read only user to query the views in Synapse SQL Serverless pool (Error says Cannot find the CREDENTIAL)

Silvano P 101 Reputation points
2022-08-17T20:36:47.307+00:00

Hello,

in my Database in Synapse Serverless SQL Pool I have several views that read to parquet files in the ADLS2 and everything works fine.

I Created my Credential couple of years ago and I don't remember which type of Credential (Database Scoped or Server Scoped).

How can I determine the name and type?

I just created a Login and User with a db_datareader Role but keep getting the following error

Cannot find the CREDENTIAL 'https://xxx.dfs.core.windows.net/sample/parquet/*.parquet', because it does not exist or you do not have permission.
Msg 4413, Level 16, State 1, Line 33
Could not use view or function 'dbo.vwMyViewXXX' because of binding errors.

I think I should do something similar

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{credential_name}] TO [{user}];

Thanks for help

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,316 questions
{count} votes

1 answer

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 25,796 Reputation points Microsoft Employee
    2022-08-18T22:16:58.393+00:00

    Hello @Silvano P ,

    Welcome to the MS Q&A platform.

    You can check the database level credentials using the below query.

    select name, credential_identity from sys.database_scoped_credentials

    Here, name is the credential_name of the database scoped credentials.

    Once you find the credential name, then you can execute the below query by replacing the credentail_name and username

    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[credential_name] TO [username];

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments