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

Question

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

Answer 1

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 or upvote 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