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.

  • From your mention of AD I assume you are talking about SQL Server. Is that correct? What version of SQL Server and Windows are you using? Jun 15, 2012 at 15:39
  • SQL 2008 and Windows 2008. Thanks for your help
    – DBAuser
    Jun 15, 2012 at 15:43

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

  • Thanks. Good information. I agree with the certificate authentication but to use the certificate, you need to use the password which means it is open again and anyone could use this in their code, correct? Also, I would think this solely depends on the types of business and applications that are used. If an environment and the security is built with the needs for developers with minimal coding change to the existing code, such solutions often are difficult to get implemented. Thanks once again for your time
    – DBAuser
    Jul 18, 2012 at 13:59
  • No, password is not required. You can encrypt certificate by master key (DMK) and to SIGN modules you need control rights on certificate. Usage of signed modules doesn't change- the only difference is that you sign module every time if you change it.
    – Jānis
    Jul 18, 2012 at 14:25
  • Sorry. You are right. I was confusing this with application role security.
    – DBAuser
    Jul 18, 2012 at 15:58
  • @Jānis about certificates...how/where you store the certificate passwords?
    – Muflix
    Jan 18, 2021 at 16:47
  • @Muflix lately using 1password.com for that.
    – Jānis
    Jan 20, 2021 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.