How to return dataset on a remote database table with different user login

• Wenjing Lin

  SSC Enthusiast

  Points: 188

  More actions

  March 2, 2009 at 11:35 pm

  #200842

  I have two databases on two different servers. For example A and B. On A server the database uses window authentication and B server database is database login user and password. I need to return dataset from B server database into A server database. The stored procedure is in A server. The stored procedure will need to select data from B server. Can someone let me know what is the syntax and how to get this working? Thank you.

• dharani-919169

  Old Hand

  Points: 302

  More actions

  March 3, 2009 at 12:42 am

  #952247

  Hi

  for this scenario please use linked server concept

  EXECUTE THIS in server A

  STEP1:

  exec sp_addlinkedserver 'name of server B', 'SQL SERVER'

  STEP2:

  EXEC sp_addlinkedsrvlogin

  @rmtsrvname = 'name of server B',

  @useself = 'False',

  @locallogin = 'domainname\loginname',

  @rmtuser = 'login name of server B',

  @rmtpassword = 'Password of server B'

  STEP3:

  when u access objects of server B in server A

  then access like this

  sreverB.tablename.columnname