70-465 · Question #46
You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements. What should you recommend?
The correct answer is A. Create stored procedures that use EXECUTE AS clauses.. To allow application users to perform UPDATE operations while meeting specific business requirements (such as restricting direct table access), stored procedures with EXECUTE AS clauses are the recommended approach. This grants controlled, elevated permissions at the procedure le
Question
Options
- ACreate stored procedures that use EXECUTE AS clauses.
- BCreate a user-defined database role and add users to the role.
- CCreate functions that use EXECUTE AS clauses.
- DCreate a Policy-Based Management Policy.
How the community answered
(51 responses)- A76% (39)
- B4% (2)
- C14% (7)
- D6% (3)
Why each option
To allow application users to perform UPDATE operations while meeting specific business requirements (such as restricting direct table access), stored procedures with EXECUTE AS clauses are the recommended approach. This grants controlled, elevated permissions at the procedure level without exposing underlying table permissions directly to users.
Stored procedures with EXECUTE AS clauses allow the procedure to run under a specified security context (e.g., a more privileged user or owner), enabling application users to perform UPDATE operations on tables without being granted direct UPDATE permissions on those tables. This pattern enforces the principle of least privilege by channeling data modifications through controlled code paths, satisfying typical business requirements around auditing and access control.
Creating a user-defined database role and adding users grants direct permissions on the tables themselves, which does not restrict how users interact with the data and likely violates business requirements around controlled access.
User-defined functions in SQL Server cannot perform DML operations such as UPDATE on base tables, making them technically incapable of fulfilling the requirement to allow UPDATE operations.
Policy-Based Management is used to enforce and evaluate configuration standards and compliance policies for SQL Server instances, not to manage or grant data access permissions for DML operations like UPDATE.
Concept tested: SQL Server EXECUTE AS clause for controlled DML access
Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql
Topics
Community Discussion
No community discussion yet for this question.