You are the administrator of your company network. You are in charge of a SQL Server 2008 instance that contains a database named DBx1. DBX1 contains a stored procedure called ProcedureX1 and a table named TableX1. By using a sp_executesql Transact-SQL statement, ProcedureX1 selects data from TableX1. As you know, there’s a rule in your company, which is users are not permitted to access tables directly in any database.When ProcedureX1 is run by users, the following exception pops up: “Msg 229, Level 14, State 5, Line 1The SELECT permission was denied on the object ‘Table1’, database ‘DB1’, schema ‘dbo’.”
Now you must enable the user to execute ProcedureX1 successfully, while you have to make sure that it will not break the rule of the company. So what should you do to achieve this?
A.
You should execute the GRANT SELECT ON dbo.TableX1 TO User1 Transact-SQL statement.
B.
You should alter ProcedureX1 and add the WITH EXECUTE AS OWNER option to its header.
C.
You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
D.
You should alter ProcedureX1 and add the EXECUTE AS USER = ‘dbo’ option immediately before the call to the sp_executesql stored procedure.