Access one Database Server Object in another Database server

Access one Database Server Object in another Database server

To access one DB Server from another DB server we required Linked Server object , whcih was created to access another DB Server
Below are the methods to Create Linked Servers

Required System stored Procedure "sp_addlinkedserver" Stored Procedure.


Ex : -- By Windows Authentication

EXEC sp_addlinkedserver @server= 'LinkedServer',
@srvproduct='',
@provider='SQLNCLI', -- SQL Client
@datasrc='<< Provide DB Server IP>>',--the data source
@provstr='Integrated Security=SSPI;'

Ex : -- By Sql Authentication

exec sp_addlinkedserver
@server = 'LINK2',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=<<serverip>>;UID=<<USERID>>;PWD=<<PASSWORD>>;'


After this you need to provide DB Server Authentication settings for Windows Login User
i) Create a Login For the Windows User
ii) Provide the sysadmin role in Server roles.
iii) Map the Database to that user to access in User Mapping.

 Write the Query to access the table in Second DB Server.

like
Select * from <<LinkedServer Name>>.<<Database Name>>.Dbo.<<TableName>>