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.
A.
Contained databases
B.
Application roles
C.
Cross-database ownership chaining
D.
Digital certificates
Explanation:
An application role is a database principal that enables an application to run with its own,
user-like permissions. You can use application roles to enable access to specific data to only
those users who connect through a particular application. Unlike database roles, application
roles contain no members and are inactive by default.
I think C.
It is differently C
since we want to grant users on DB1 the exec for store procedure on DB2 without grant the select on DB2
No I am so sorry it is B
Yes It is C
think B is right
I think that C is correct.
Application roles are created in database and are database centric.
Cross database ownership chaining was designed excactly to do this. There is no need to granting users direct access to the tables as far as the owner of the table is the same.
Application roles can directly access another database only if a guest account exists in the second database.
https://msdn.microsoft.com/en-us/library/bb669062(v=vs.110).aspx
Option B is right
Because An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default.
Reference: Application Roles
B is wrong. You need an Application Role only if the stored procedure would be used to access the table via a 3-party application.
The issue here is about granting access to a table owned by another schema: Schema-based Ownership Chaining. Option C ensures that.
My Answer is B as well
[email protected]
B cannot be correct:
https://msdn.microsoft.com/de-de/library/ms188908.aspx
‘sp_setapprole cannot be executed within another stored procedure or within a user-defined transaction.’
Therefore C seems to be the correct answer.