70-465 Question #81: Real Exam Question with Answer & Explanation

Question

You need to enable users to modify data in the database tables using UPDATE operations. You need to implement a solution that meets the design requirements. What should you configure?

Options

• AYou should configure a server role.
• BYou should configure a database role.
• CYou should configure functions that use the EXECUTE AS statement.
• DYou should configure stored procedures that use the EXECUTE AS statement.

Explanation

To enable users to modify database data using UPDATE operations while meeting specific design requirements, configuring stored procedures with the EXECUTE AS statement provides a secure and controlled solution.

Common mistakes.

• A. Server roles grant broad permissions at the server level, which is excessive and inappropriate for granting granular table modification access to end-users.
• B. While database roles can grant UPDATE permissions, this typically provides direct table modification access to all members of the role, which might not align with design requirements for controlled, indirect data manipulation through specific application logic.
• C. User-Defined Functions (UDFs) are primarily for computations and returning values, and generally cannot perform data modification operations like UPDATE, even when combined with the EXECUTE AS statement.

Concept tested. Secure data modification using stored procedure impersonation

Reference. https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql

No community discussion yet for this question.