Solved

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

Posted on 2010-08-20
4
442 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
  • 2
4 Comments
 
LVL 28

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 74
MS SQL page split per second is high 19 98
SQL Server 2012 express 24 39
View SQL 2005 Job package 16 44
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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