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 exactlyas 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 identifywhat permissions a stored procedure will have whenit
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.