Exam DP-203 topic 3 question 40 discussion

"Caller must have REFERENCES permission on credential to use it to authenticate to storage." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

When you're using the OPENROWSET function to read data from the storage account, you're actually performing a read operation, not an execute operation. The credential is used implicitly by Azure Synapse to authenticate the session with the storage account and does not require the EXECUTE permission for the user or group accessing it. Instead, you grant permissions that are appropriate for data access. In this case, the SELECT permission is the correct one to use because it allows the members of Group1 to read or select the data.

Although SELECT may be required *IF* the groups need to query the external table, the question says "ensure that members of Group1 can read CSV" [using the credential]. Because of this, the group must have REFERENCES permission to reference the credential in the OPENROWSET setting. Remember that in the real world, a group can create a table for another group to access. Note: control would give access, but breaks the "least privilege" principle.

Yes both require execute and reference. I am starting to feel this is a trick question. I think I will choose 'execute' due to the fact of the following wording 'The principle of least privilege must be followed.

REFERENCES

the table give it out, reference https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-overview?tabs=impersonation

Database users who access external storage must have permission to use credentials. To use the credential, a user must have the REFERENCES permission on a specific credential. To grant the REFERENCES permission on a server-level credential for a login, use the following T-SQL query in the master database: GRANT REFERENCES ON CREDENTIAL::[server-level_credential] TO [login_name]; To grant a REFERENCES permission on a database-scoped credential for a database user, use the following T-SQL query in the user database: GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[database-scoped_credential] TO [user_name];

Caller must have REFERENCES permission on credential to use it to authenticate to storage.

I am new this topic, however to read from a file you should grant Execute on the above level. so Storage1 should grant Execute on file level should be read.

D , SELECT