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.
Question
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)- A80% (37)
- B4% (2)
- C2% (1)
- D13% (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.
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.
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.
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.
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
Community Discussion
No community discussion yet for this question.