0

I am trying to understand the linked-servers in SQL Server Studio 2012 that I am using to develop a big report. I have looked through some of the post but I didn't see anything related to permission or how to do it with non-default instance servers that we have. My first question is: can I use create linked-server querying when I only have read permission? Second: I would appreciate if someone can elaborate more on the syntax. Below is the syntax that I am having problem with. Our server name is not just the server name but it has servername\databasename due to IT's reason for a non-default instance (this is what our IT department told me).

SELECT *
FROM   [server1\databse1 name].[dbo].[table name]. tab1
       INNER JOIN [server2\database2 name].[dbo].[table name] tab2
       ON tab1.ID = tab2.ID`

Thank you so much.

bumble_bee_tuna
  • 3,533
  • 7
  • 43
  • 83
Thao Lil'
  • 17
  • 1

1 Answers1

0
  1. can I use create linked-server querying when I only have read permission?

Answer: No you will need sysadmin permissions to add a linked server. If you only have read permissions on the server you will need to ask your DBA to do it for you.

  1. I would appreciate if someone can elaborate more on the syntax. Below is the syntax that I am having problem.....

Your syntax will be something like this...

Assuming databse1Name is located on the Linked server and database2Name is on the server where you are logged on. 

SELECT * FROM 
[ServerName].[databse1Name].[dbo].[table name]. tab1 
INNER JOIN [database2Name].[dbo].[table name] tab2 
ON tab1.ID = tab2.ID

If it is a specific Instance on the linked server then you would write your query something like...

SELECT * FROM 
[ServerName\InstanceName].[databse1Name].[dbo].[table name]. tab1 
INNER JOIN [database2Name].[dbo].[table name] tab2 
ON tab1.ID = tab2.ID
M.Ali
  • 67,945
  • 13
  • 101
  • 127