Post reply

T SQL FOR JOINING DIFFERENT TABLES on different databases with different credentials

  • January 24, 2011 at 11:07 am

    #233222

    I want to join two tables residing on different databases but on the same server but the authentication is different for the tow databases.

    In detail:

    SERVERA -- DATABASE A -- TABLEA (AUTHENTICATION USING SQL SERVER ,LOGIN = A,PWD = A)

    SERVERA -- DATABASE B -- TABLEB (AUTHENTICATION USING SQL SERVER ,LOGIN = B,PWD = B)

    I WANT TO JOIN TABLEA and TABLEB without using linked servers.Please help me

  • January 24, 2011 at 11:21 am

    #1277387

    I think linked servers would allow you to change authentication information, but you didn't want to do that...

    The openrowset command lets you specify the connection information, you might roll both connections into a pair of CTE's

    otherwise, you might create a stored procedure featuring EXECUTE AS USER=[UserWithCrossDatabaseAccess]

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master',

    'Set FmtOnly OFF;EXEC dbo.sp_Who')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 24, 2011 at 11:55 am

    #1277404

    Thank you lowell but do i need to be an DBA to run this adhoc queries

  • January 24, 2011 at 12:32 pm

    #1277419

    to use openrowset or openquery, adhoc would need to be enabled; if that is not possible, then linked servers, or using a login that has access to both databases would be required.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 24, 2011 at 1:54 pm

    #1277458

    This is basically the exact reason linked servers exist.

    Why don't you want to use them?

Viewing 5 posts - 1 through 4 (of 4 total)

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