Member Avatar for am_dumb

hy, i have 2 database. DB1 & DB2.
i create SP in DB1. in the SP, i want to access table "TBL" in DB2.
how can i create that SP??

CREATE PROCEDURE test 

AS
BEGIN
??????????
END;

Thx,

  • 2 Contributors
  • 2 Replies
  • 105 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by am_dumb

Dani AI

Generated

As already demonstrated and confirmed, a stored procedure in one database can read objects in another. A few practical tips will help avoid permission headaches and make the code easier to maintain.

Create a local synonym in DB1 that points to the DB2 table. That lets your proc use a short name and makes it simpler to change the target later:

CREATE SYNONYM dbo.TBL FOR DB2.dbo.TBL;

If callers of the procedure should not have direct rights on DB2, run the proc under a different execution context instead of granting broad rights to every user. One approach is to create the proc with an explicit execution context:

CREATE PROCEDURE dbo.test
WITH EXECUTE AS OWNER
AS
BEGIN
  -- reference the external object here
END;

Important cautions and troubleshooting checklist:

  • Confirm the account that executes the proc has rights on the target object, or use EXECUTE AS / module signing if you want to avoid granting users cross-database permissions.
  • Do not rely on changing the database with USE inside a proc; prefer schema-qualified names or synonyms.
  • Cross-database ownership chaining can remove the need for extra grants but should be enabled and audited deliberately.
  • If DB2 lives on a different SQL Server instance, set up a linked server (distributed queries are slower and require extra configuration).

Further reading: see Microsoft documentation on creating synonyms (CREATE SYNONYM), execution context (EXECUTE AS), cross-database ownership chaining (), and linked servers (sp_addlinkedserver).

Recommended Answers

All 2 Replies

Member Avatar for samaru

The syntax

SELECT * FROM [DATABASENAME].[SCHEMA].[TABLE]

So let's say you're in the Northwind database and you wanted to query a table in the Pubs database, you would do the following:

SELECT * FROM Pubs.dbo.Authors
Member Avatar for am_dumb

thx,

its work!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.