cross db access using signed proc(s)


  • my setup is straight forward...db 'A' needs access to db 'B'...that is, read some data outta 'B' via 'A'.  I dont' wanna enable trustworthy or create a common user in both db's...

    db 'A' is the initator...therefore they I've created a cert there and all calling proc(s) from there are signed against said cert...cert is backed up and installed on 'B'...a user is created for the cert in 'B' and granted the approp perms...good..

    All the proc(s) in 'A' are created w/'execute as owner' syntax...

    using a 'test' user In 'A'...invoke the proc(s) in 'A' that read from 'B' all works.  Data access in 'B' is happening...

    I decide to change/remove the 'execute as owner' from the proc(s) and re-enable the cert binding after the drop/create of proc(s) problem...I run a quick query to verify that the proc(s) are signed...good result..

    but when I re-run using my 'test' complains that user 'test' doesn't exist in db 'B'!!???

    but when I return the 'execute as owner' to all proc(s), re-bind the proc works...

    I seem to be missing something fundamental!?  I thought the 'authenticator' would be my cert...regardless of the 'execute as ...' least that is what the docs have lead me to believe.

    docs state the cert is 'unioned' in the user_token...but I don't see it.  I'm think'n my 'add signature...' is the fault!?

    can anyone lend some insight? this is straightforward stuff...but i'm baffled!!!

    thanks in advance.  Everything is owned by 'dbo'...


    mike t.

    Wednesday, January 11, 2017 10:06 PM

All replies