DP-800: Developing AI-Enabled Database Solutions

By Microsoft · Question Mode

QuestionQ82

VerifiedSecure, optimize, and deploy database solutions

You have an Azure SQL database that contains a table named Sales.Customer. The table has columns named CustomerId, FullName, Email, TaxID, and RegionId.

A support application uses a database role named AppSupport.

Implement a security solution for AppSupport that meets these requirements:

  • Prevent AppSupport from viewing TaxID.
  • Allow AppSupport to query Sales.Customer for troubleshooting.
  • Allow AppSupport to run the Sales.usp_GetCustomerByCustomerId stored procedure.

Which Transact-SQL statements should the solution include?

Select
To run Sales.usp_GetCustomerByCustomerId:
To query Sales.Customer:
To prevent viewing TaxID:
Explanation

GRANT EXECUTE permits the stored procedure to run, and GRANT SELECT permits troubleshooting queries against the table. A column-level DENY SELECT on TaxID overrides the table-level permission on the same entity, preventing the role from selecting that sensitive column.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!