You have a SQL Server database. The database contains two schemas named Marketing and Sales. The Marketing schema is owned by a user named MarketingManager. The Sales schema is owned by a user named…

Question

Options

• AMarketing.GetSalesSummary should be created by using the EXECUTE AS 'SalesUser' clause.
• BMarketing.GetSalesSummary should be created by using the EXECUTE AS OWNER clause.
• CMarketing.GetSalesSummary should be created by using the EXECUTE AS CALLER clause.
• DMarketing.GetSalesSummary should be created without an EXECUTE AS clause.

• A
  84% (59)
• B
  10% (7)
• C
  4% (3)
• D
  1% (1)

Explanation

1. When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module. So John should be granted EXECUTE permission on Marketing. GetSalesSummary stored procedure. 2. Additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. The user executing the module is, in effect, impersonating the specified user. Because John is not granted a SELECT permission on the Sales.Orders table which is referenced by the stored procedure, EXECUTE AS CALLER is not suitable. (CALLER specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.) Because the user named SalesUser DOES have SELECT permission on the Sales.Orders table, he can be specified in EXECUTE AS clause. It means that Marketing. GetSalesSummary stored procedure should be created by using the EXECUTE AS 'SalesUser'

No community discussion yet for this question.