I need to give read only access to view which resides on my server to my client. So the obvious way to do is by using Linked Server on the client machine. I successfully created it by using
EXEC sp_addlinkedserver @server = 'MyServer_Name'
Now client server able to access records from view using
SELECT * FROM [MyServer_Name].[Database_Name].[dbo].[View_Name]
After creating Linked Server, my client able to view all databases & there resp tables & views under Catalogs menu of created Linked Server. I want to hide all this information under Catalog menu.
How to achieve this?
Is there any other way besides Linked Server to grant read only access to view.
Thanks in advance.
Why do they need or want a linked server? What are they doing with the data that they need to access it from their system?
Ideally - you would grant them SELECT access on that view only - based on their login to your system. Then they would use SSMS to access your system and select from the view.
If they need to join to this data from within their system then they need to copy that data from your system to their system on a scheduled basis (e.g. SSIS extract) and again using the login you set up with SELECT only access on that one view.
It appears that you created a linked server and setup the permissions on that linked server to give them full access to everything in your system. That is why they can see everything...and if you just have to have a linked server then make sure the authentication is through a limited account that only has access to select from that one view.