0

We have a SQL Server 2012 instance which is the database for our security system. Now we have a scenario where we want to allow another team our SQL Server to select certain records, ie we created four views for them for their requirements.

But, we don't want to provide or list all our tables to them but only allow these four views to see and query in our system. As I go through many documents it's clear that we can do it through making our SQL Server as a linked server.

What's the best way to do it through a linked server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aryan
  • 49
  • 6

2 Answers2

1

Are the views in a specific schema? Try to grant the users access to that schema, so that they can only read and see meta from it.

https://learn.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration

Martin Thøgersen
  • 1,538
  • 18
  • 33
1

we don't want to provide or list all our tables to them but only allow these four views to see and query in our system.

You don't need linked server to accomplish this.

All you need is to grant SELECT permission on these 4 views.

When you create a user in a database and don't grant any permission to it, it does not see any user table/view at all. And it will be able to see and query only those objects on which you'll grant the permissions

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • So, in which case linked server is implemented ? – aryan Oct 02 '17 at 19:44
  • Linked server is implemented when you have 2 servers and want to elaborate data from both of them. i.e. you need to write queries like select * from server2.RemoteDB.dbo.myRemoteTable join dbo.myLocalTable – sepupic Oct 02 '17 at 19:47