Executing a Stored Proceedure from another database???

Question

Post reply

Executing a Stored Proceedure from another database???

Jeremy Conway

Grasshopper

Points: 16
More actions
April 12, 2005 at 12:35 am

#67185

Hi all,
I have a operational system which for name sake uses a database called database1. This database contains both tables and stored proceedures.
There is also a 2nd database called database2 which only contains views of some of the tables contained in database1.
A 2nd operational system interfaces with the views in database2 which works fine, however, it requires to run 1 stored proceedure that is in database1.
I know the easy solution is that I could create a copy of the stored proceedure in database2. (whereby it updates the tables in database 1 via the views) But what I am trying to avoid is that if this proceedure is ever modified then it will have to be modified in two databases.
My question is whether it is possible to create a stored proceedure (or by some other method) which can be called by the application using database2 that executes the stored proceedure in database1???
Is this possible? The application can only access 1 database so it is not possible to use a different session connecting to database1 inorder to run the stored proceedure.
Any assistance would be greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Try something like following, you can put them in proc as well.

use database2

go

exec database1.dbo.proc1

Karl Grainger SSC Enthusiast Points: 194 More actions · Answer 2

Jeremy

One extra point to add. If your user is only in database2, then you will need to use cross db ownership chaining to allow the procedure from database2 to call the procedure in database1.

Have a look in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_8d7m.asp for details - note this only works with SP3 though!

HTH

Jeremy Conway Grasshopper Points: 16 More actions · Answer 3

Thanks Guys, I thought the solution would be far more complex and overlooked the simple solution.

Anyway, I've got it working. Thanks again.

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 4

You can better add the user to the second database than enabling cross db ownershipp chaning. This is quite a drastic measure because it affects the whole server and thus all databases. From a security point of view, better add the user to the second database with restricted rights (just execute rights to the procedure).

Jeremy Conway Grasshopper Points: 16 More actions · Answer 5

Yep thats what I did. As you said I thought cross db ownership chaining was a bit drastic in this instance.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

One also wonders why there is two db's when the second db only contains views of objects in the first db...?

Better (but I'm sure for one reason or other not possible) solution would perhaps be to scrap db 2 entirely and keep all in a single db instead.

/Kenneth

Jeremy Conway Grasshopper Points: 16 More actions · Answer 7

Because the application that uses the 2nd db creates 40 tables per user. As this is a secondary application which our team does not have any role in supporting other than providing connectivity we did not want all these tables cluttering up the primary database that we are responsible for.