I have managed to do a 2 database query. Now I want to do the same thing with another 2 databases but the userid and passwords to the 2 databases are different. What is the syntax for specifying the userid and password for the DB that is not the DB the query is running in? I am doing this in 2000 Enterprise Manager.
We are talking databases not instances, correct?
Really, you should have a single login that is mapped to users in both databases with sufficient permissions to execute the query. Personally I would, in conjunction with your dba, sort out the permissions issue.
Otherwise, I suppose you could use OPENROWSET (Transact-SQL)