Database Fundamentals — Question 148

You have developed a stored procedure named usp_GetEmp that accepts an employee number as a parameter and retrieves the details about the employee from the CurrentEmp table of a database named Employees.
You have tested it, and it works exactly as you expected.
Later, another employee tries to use the stored procedure and receives the following error:
The SELECT permission was denied on the object 'CurrentEmp', database 'Employees.schema 'dbo'
What should you do to resolve the problem?

Answer options

• A. Modify usp_GetEmp to include the With Execute As Owner clause
• B. Modify usp_GetEmp to include the With Execute As Caller clause
• C. Grant the employee the SELECT permission on the CurrentEmp table
• D. Grant the employee the SELECT permission on the Employees database.

Correct answer: A

Explanation

The correct answer is A because using 'With Execute As Owner' allows the stored procedure to run with the permissions of its owner, bypassing the restrictions faced by the caller. Options B and C would not grant the necessary permissions to the stored procedure, and option D would grant broader permissions than necessary, which is not a best practice.