Can a Stored Procedure Work with Two Different Databases? How About Two Servers

Can a stored procedure work with two different databases? How about two servers?

If we're talking about two databases on the same server: yes, a stored procedure can access another database. You have to make sure that the user under whose privileges the procedure is being run has the necessary privileges on each database.

For example, suppose you have two databases on the same server, mydb1 and mydb2, and that each contains a table named messages with the same structure. Suppose you want to add a stored procedure to mydb2 that empties the messages table in mydb2 and copies the contents of the messages table in mydb1. You could do this:

CREATE PROCEDURE `SynchroniseMessages` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `mydb2`.`messages`;

INSERT INTO
    `mydb2`.`messages`
    SELECT * FROM `mydb1`.`messages`;

END

See how I've fully qualified the tables with the databases to which they belong. In fact you could argue that I'm being over-zealous here, because we specified that this stored procedure will belong in mydb2. I don't need to add the mydb2. qualifier. If the stored procedure were in the mydb1 database, I would need those qualifiers, but conversely I wouldn't need the mydb1. where it appears.

In order to be able to run this procedure (possibly in order to be able to define it?), I'd need to make sure my user has DELETE and INSERT privileges on mydb2, and also SELECT privileges on mydb1.

Databases on different servers sounds rather more complicated.

Possible with multiple database connections

I commented already, but I would like to add some more. So as I said already, that you can do anything that fits into the single transaction.

But for your case I would suggest to create synonyms for every cross database/instance object and then use synonyms everywhere.

I've created following function to mock view/tables synonyms. It has some limitations but at least it can handle simple use cases.

CREATE PROCEDURE [tSQLt].[FakeSynonymTable] @SynonymTable VARCHAR(MAX)
AS
     BEGIN

         DECLARE @NewName VARCHAR(MAX)= @SynonymTable+REPLACE(CAST(NEWID() AS VARCHAR(100)), '-', '');
         DECLARE @RenameCmd VARCHAR(MAX)= 'EXEC sp_rename '''+@SynonymTable+''', '''+@NewName+''';';

        EXEC tSQLt.SuppressOutput
              @RenameCmd;

        DECLARE @sql VARCHAR(MAX)= 'SELECT * INTO '+@SynonymTable+' FROM '+@NewName+' WHERE 1=2;';

        EXEC (@sql);

        EXEC tSQLt.FakeTable
              @TableName = @SynonymTable;
     END; 

stored procedure for different databases

Yes, as long as you have permissions. You can also do cross server if you have a Linked Server setup.

Cross DB Example:

SELECT * FROM localTable as lt
INNER JOIN otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Cross Server example (assumes you have created the linked server):

SELECT * FROM localTable as lt
INNER JOIN ServerName.otherDB.dbo.OtherTable as ot
ON lt.ID = ot.id

Execute stored procedure on multiple linked servers vs separate connections

The best practice is that each stored procedure execute on his own server. Even you will call all procs from one server with linked server.

When you call a procedure that contains a linked server the data is load before the processes begin. But you call a procedure through the linked server the proc will process on the own server before answer you.

SQL Stored Procedure(s) - Execution From Multiple Databases

A system stored procedure can do what you want.

Normally, a stored procedure executes against the database it was compiled in. (As you have noticed.)
If the procedure name starts with "sp_", is in the master db and marked with sys.sp_MS_MarkSystemObject, then it can be invoked like this:

Exec somedb.dbo.sp_GetTableDocumentation
Exec anotherdb.dbo.sp_GetTableDocumentation

See: https://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/

This is all fine if you can accept putting your stored procedures into master.

Stored Proc access multiple databases on same server FAILS

LimitedUser needs permissions on Database2 to do whatever the stored procedure is doing in that database, ownership chaining will only work within the same database (unless you enable the server option Cross Database Ownership Chaining, which I don't recommend as it breaks down the database container as a security boundary).

So, for example, you have db1 and db2, there is a stored proc in db1 that executes select * from db2.dbo.table1

For this you need LimitedUser to have:

• execute permissions in the db1 database for the procedure
• select permissions on table1 in db2

---

Related Topics