Cross Database Chaining vs. Trustworthy Option

Question

Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account had read/write permissions to all the databases.

But now we changed all permissions to windows sign on AD group and the users in the AD group do not have all permissions to all the databases.

Example:

• AD group UG1 has 10 users who are users on Database A.
• Stored procedure PROCA resides in Database A but has selects on the tables that belongs to Database B and Database C.
• Execute permissions have been granted to the stored proc PROCA and the AD group UG1 as users (with no other permissions) has been created on Database B and Database C.
• The database owners of the DatabaseA, DatabaseB and DatabaseC are under login XXX.

As we all know, such permissions across databases can be accomplished by

• Option 1 : Switching on Database Cross chains ON all the databases, or
• Option 2 : Giving the AD group UG1 required individual object permissions on the databases (B and C) or
• Option 3 : Using Trustworthy db options on DatabaseA and changing the stored proc PROCA to include execute as “dbo” user so that the permissions can impersonate.
• Option 4: Using certficates

My question is what would be the best solution with respect to security and keeping it simple and safe?

Please note that none of the developers are database owners of any of the databases and elevated permissions are only assigned to DBA’s and hence the chances of someone hacking or SQL injecting or impersonating as sysadmin is “none”.

But again, I would like to know what your take would be in such scenarios? I prefer option 3.

Answer 1

Option 1 is the worst of all.. would not use that..

Option 2 a lot of administrative effort..

Option 3 (see last sentece :) )

Option 4 recently I had to think about how to secure databases.. My solution was this one. there is great article on security here. Even thought it was a bit complicated to use Certificates at beginning (never done with them anything before) I am very happy of this choice. I grant permissions to Certificate Login/User. I sign procedures by that certificate and everything works. I use the same system to grant permissions for dynamic SQL (so if there is sp_executesql I grant minimum rights to necessary tables to Certificate User, so caller of proc can not see tables and i do not have to make Execute as Owner or smthng). And the same to grant rights to use Asymmetric Keys. And I always know WHO was the caller of procedure (no Execute As), even though its not very important in my case..

Its a bit cumbersome to sign procedures during development time (trying to find good way around that). Currently I am searching procedure text (DDL trigger) for use of other database and if I find it, I print out warning, that procedure possibly has to be signed..

Another thing I miss is that its not possible to sign functions or views (there must be a good reason) so you cant use them to access other DB..

Last thing I would like to say- I would prefer to solve as many problems I possibly can with minimum variety of patterns.. So if you extensively using "Execute As" and proxy users then maybe Trustworthy option is better choice.