[Sql Server] Execute the SP/Query from a Linked server

 Sql Server    Linked Server

▌Environment

---

▋Windows Server 2012 Standard

▋MS Sql Server 2012

▌Implement

---

▋SQL

EXEC sp_addLinkedServer @server = 'MyLinkedSrvName', --Linked Server Name @srvproduct = '', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=TargetDbSrv', @location = NULL GO EXEC sp_addlinkedsrvlogin        @rmtsrvname=' MyLinkedSrvName',        @useself='false',        @rmtuser='TargetDb_UserId',        @rmtpassword='TargetDb_UserPwd' GO

▋Settings

Before we connect to the linked server, notice that

The user of local DB Server must be in sysadmin group

OR

Authorize the user of local DB Server to have the permission when creating the linked server.

EXEC sp_addLinkedServer @server = 'MyLinkedSrvName', --Linked Server Name @srvproduct = '', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=TargetDbSrv;User ID=MyLocalUserId', @location = NULL GO EXEC sp_addlinkedsrvlogin        @rmtsrvname=' MyLinkedSrvName',        @useself='false',        @rmtuser='TargetDb_UserId',        @rmtpassword='TargetDb_UserPwd' GO

▋Usage

▋Store Prcedure

select * from OPENQUERY  (MyLinkedSrvName, 'EXEC [DB_Instance_Name].[dbo].[usp_StoreProcedure]')

▋Sql query

SELECT * FROM [MyLinkedSrvName].[ DB_Instance_Name].[dbo].[RemoteTable]

▌Reference

---

▋Access to the remote server is denied because no login-mapping exists.