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'...it all works. Data access in 'B' is happening...
I decide to change/remove the 'execute as owner' from the proc(s)...re-install and re-enable the cert binding after the drop/create of proc(s)...no problem...I run a quick query to verify that the proc(s) are signed...good result..
but when I re-run using my 'test' user...it 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 sigs...it works...
I seem to be missing something fundamental!? I thought the 'authenticator' would be my cert...regardless of the 'execute as ...' clause...at 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'...
Read Erland's article
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
- Proposed as answer by Lin LengMicrosoft contingent staff, Moderator Thursday, January 12, 2017 7:32 AM
I used Erland's article as my base foundation...
I've actually taken his code..pasted it in, executed it and it works just fine.
When I adapt it to my environment...failure.
like stated...when I create my proc(s) using 'execute as owner' everything works fine.
I can query the meta catalogs and see that my proc(s) are indeed signed.
can anyone point me to other means by which I can debug what is happening?