Can cross database ownership chaining work when using application roles and if so, how?
My company has a two tier application that is utilizing application roles. Once the application role has been started, there is stored procedures that have to select data from other database and even launch stored procedures in other databases. It would be nice if database chaining would work instead of granting individual logins access to the other database or granting guest to the other database.
I tested the following scenario:
Created Db1 and DB2. Owner of Db1 and DB2 is sa, meaning all objects will be owned by dbo. I created application role Db1..AppRole1. I also created Db2..Tble and add 1 row of data into it. I granted login UserA connection ONLY to Db1 and DB2. Granted guest connection to DB2 and enabled cross database ownership chaining to Db2. Using Query Analyzer, I set the Db1..AppRole1 and performed "SELECT * FROM Db2..Tbl", which failed with lack of permissions. So, I granted guest SELECT on Db2..Tbl and executed the "SELECT * FROM Db2..Tbl" again, which was successful. Then I disabled cross database ownership chaining to Db2 (guest SELECT permissions to Db2..Tbl remain in tact) and executed the "SELECT * FROM Db2..Tbl", which was successful.
From the scenario above, I conclude that if I have to grant guest SELECT on an object, I do not need cross database ownership chaining enabled. What I was hoping to accomplish was to use cross database ownership chaining without granting object permissions to guest or any other users. Is this possible?