We have two DBs on different servers - S1 is a remote server with a DB we use for login authentication, S2 has our application and app-database. When you login, we check the user against S1 and then create a user object in S2 using some S1 data.
Is this something a stored procedure in S2 can do - access a remote DB and select data from it - or would we be better having the application query S1 for data and then pass it to S2 to save?
Let's say I want to copy the id and name from DB1.users to user_id and user_name DB2.game_users, the ideal would be a stored procedure in DB2 which is simply passed id as a parameter... but how can it get permission to the remote DB?
Have you looked at the FEDERATED engine?
