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?

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.

Explanation:
The Execute As OWNER clause can be used to identify what permissions a stored procedure will have
when it is executed. The Execute As
Owner clause results in the stored procedure running with the same permissions as the owner of the
stored procedure, or the account that
created the stored procedure. The EXECUTE AS permission is placed in the line right after the
CREATE PROCEDURE line as follows:
CREATE PROCEDURE ….
WITH EXECUTE AS ….
Since you created the stored procedure and it worked when you tested it, it will work if it is
executed with your permissions.
Answer C is incorrect. While it may be possible to grant the SELECT permission to the table, it would
not help if another user executes
the stored procedure. The next user will have the same problem. Additionally, a common method of
protecting databases is not to grant
access to the tables directly, but instead grant access via stored procedures or views.
Answer B is incorrect. The SELECT permission is granted to tables or views, not entire databases.