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.
- 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.