Cross Database Procedure exexcution

Question

Cross Database Procedure exexcution

kasamsandeep

SSC Rookie

Points: 30
More actions
August 27, 2016 at 10:47 am

#329321

Hello Everyone,
I have 2 Databases DB1 & DB2 (Owner is 'sa' for both)
Table1, SP1 both exists in DB1 (Schema is 'dbo')
SP2 exists in DB2 (Schema is 'dbo')
SP1 - Does a select on Table1
SP2 - It is created 'With Execute AS Self' and Calls SP1 (Execute DB1.dbo.Sp1)
Create Procedure SP2
With Execute AS Self
as
Exec DB1.dbo.SP1
When I execute SP2, I get error "The server principal "sa" is not able to access the database "DB1" under the current security context."
I tried enabling Cross Database ownership at both Server and DB level and still not working.
Please help me, thanks in advance.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

kasamsandeep (8/27/2016)
Hello Everyone,
I have 2 Databases DB1 & DB2 (Owner is 'sa' for both)
Table1, SP1 both exists in DB1 (Schema is 'dbo')
SP2 exists in DB2 (Schema is 'dbo')
SP1 - Does a select on Table1
SP2 - It is created 'With Execute AS Self' and Calls SP1 (Execute DB1.dbo.Sp1)
Create Procedure SP2
With Execute AS Self
as
Exec DB1.dbo.SP1
When I execute SP2, I get error "The server principal "sa" is not able to access the database "DB1" under the current security context."
I tried enabling Cross Database ownership at both Server and DB level and still not working.
Please help me, thanks in advance.

Instead of CROSS Database Ownership, you'll need to enable DB1 as "Trustworthy". There are some security ramifications that you need to read about but aren't usually a problem. Recommend you Google that.

Not sure that you actually need the EXECUTE AS SELF thing, either. That's the default. Removing it might solve your problem without the "Trustworthy" thing but you'll have to test because I don't remember for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)