MSSQL Server to MSSQL Server Instance connectivity with Linked Server

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.


Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.

5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.

6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
  7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!