70-465 Question #122: Real Exam Question with Answer & Explanation

Question

You have two databases named DB1 and DB2 that are located on the same server. You plan to create a stored procedure named SProc1 in DB1. SProc1 will query a table named Table2 in DB2. You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

Options

• AContained databases
• BApplication roles
• CCross-database ownership chaining
• DDigital certificates

Explanation

This question tests knowledge of SQL Server security mechanisms that allow stored procedures to access cross-database objects without exposing underlying tables directly to users.

Common mistakes.

• A. Contained databases isolate users and authentication within a single database boundary, but they actually restrict cross-database access rather than enabling it, making them unsuitable for this cross-database query scenario.
• C. Cross-database ownership chaining can enable cross-database object access without explicit grants, but it requires the ownership chain to be unbroken across both databases and is considered a broader security risk since it implicitly grants access rather than tightly scoping it through a controlled mechanism like an application role.
• D. Digital certificates can be used to sign stored procedures and enable cross-database access by mapping certificate-based users, which is a valid technical approach, but it is more complex to implement and less directly aligned with the goal of controlling user access through a role-based abstraction compared to application roles.

Concept tested. SQL Server cross-database access control without direct table permissions

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/application-roles

No community discussion yet for this question.