Solved

How To Create A MSSQL Linked Server For Only One Database Or One Table?

Posted on 2010-08-20
4
451 Views
Last Modified: 2012-05-10
Greetings,

What is the syntax to create a linked server that will only allow a user to access one specific database or one particular table within a MSSQL user database?
0
Comment
Question by:Omega002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 250 total points
ID: 33487718
Hi,

Try this:
SELECT Count(*) FROM OPENQUERY('name of linked server', 'SELECT * FROM 'name of linked server'.'table or view name')[/

Let me know if you  have questions
0
 
LVL 2

Assisted Solution

by:bartjoosen
bartjoosen earned 250 total points
ID: 33487739
How about setup an ODBC connection for each user (user dsn instead of system dsn), and grant privileges for each user to the specific database, blocking public?

Bart
0
 

Assisted Solution

by:Omega002
Omega002 earned 0 total points
ID: 33487741
What is the syntax to create a linked server that will only give you access to one user database and what is the syntax to create a linked server that will only give you access to one table within a user database?
0
 

Accepted Solution

by:
Omega002 earned 0 total points
ID: 33487808
creating user DSN won't work if the user is connected to server A database emp2 and wants to query data on server B emp2 database while connected within emp2 on server A.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question