nerdexam
Microsoft

70-465 · Question #63

You need to recommend a solution to minimize the amount of time it takes to execute USP_5. What should you include in the recommendation?

The correct answer is A. Enable cross-database chaining. When a stored procedure accesses objects across multiple databases, ownership chaining can eliminate repeated security checks, reducing execution overhead. Cross-database chaining extends this benefit across database boundaries.

Submitted by parkjh· Mar 5, 2026Design and implement database solutions for SQL Server

Question

You need to recommend a solution to minimize the amount of time it takes to execute USP_5. What should you include in the recommendation?

Options

  • AEnable cross-database chaining.
  • BUse a server role to group all logins.
  • CUse the EXECUTE AS clause in USP_5.
  • DCopy USP.5 to each database.

How the community answered

(46 responses)
  • A
    80% (37)
  • B
    4% (2)
  • C
    2% (1)
  • D
    13% (6)

Why each option

When a stored procedure accesses objects across multiple databases, ownership chaining can eliminate repeated security checks, reducing execution overhead. Cross-database chaining extends this benefit across database boundaries.

AEnable cross-database chaining.Correct

Cross-database ownership chaining allows SQL Server to skip permission checks on intermediate objects when the owner chain is unbroken across databases, reducing the security validation overhead each time USP_5 accesses objects in other databases. Without it, SQL Server must verify permissions at every cross-database boundary, adding latency. Enabling cross-database chaining removes these redundant checks and minimizes execution time.

BUse a server role to group all logins.

Using a server role to group logins simplifies permission management and administration but does not reduce the number of security checks performed at runtime during stored procedure execution, so it would not minimize execution time.

CUse the EXECUTE AS clause in USP_5.

The EXECUTE AS clause changes the execution context of the stored procedure for security impersonation purposes, but it does not eliminate cross-database permission checks and would not reduce the execution time caused by inter-database security validation.

DCopy USP.5 to each database.

Copying USP_5 to each database would create maintenance complexity and redundancy, and would not address the cross-database permission check overhead that is causing the slow execution time.

Concept tested: Cross-database ownership chaining to reduce execution overhead

Source: https://learn.microsoft.com/en-us/sql/relational-databases/security/cross-database-ownership-chaining

Topics

#Cross-database ownership chaining#Stored procedure performance#Performance tuning#Database security

Community Discussion

No community discussion yet for this question.

Full 70-465 Practice