Post reply

Linked server in sqlserver 2008

  • March 5, 2010 at 3:43 am

    #221247

    Hi

    I have two databases from two servers. i want to use distributed queries. here i will retrieve data based on two database tables like

    select TABLEA.COL1,TABLEB.COL2 from SERVERA.DBO.DB1.TABLEA as TABLEA.COL1 INNER JOIN TABLEB

    TABLEB --> FROM LOCAL DATABASE

    TABLEA--> FROM REMOTE DATABASE

    For this scenario, i want to create linked server between SERVERA and SERVERB

    i want to know what kind of user realted permissions i have to give on both databases. do i need to give permission to system databases also like master on both server.

    i can not give admin permissions to those users.

    please guide me.

    Thanks

    Rock....

  • March 5, 2010 at 3:49 am

    #1129067

    You only have to give permissions on the table that you wish to link to. See BOL for

    sp_addlinkedserver and sp_addlinkedsrvlogin

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • March 5, 2010 at 5:42 am

    #1129096

    i think no. we must give some permissions to master database to the remote user on remote server. before giving the permission to master database i.e. RsExecRole etc., i am getting the below error.

    login failed for user ''.(Microsoft SQL Server,Error:18456)

    After giving database RSExecRole onmaster and dbowner on both databases. it works

    Thanks

    Rock..

Viewing 3 posts - 1 through 2 (of 2 total)

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