title: SQL Server select from linked server throw error if user has "deny view definition"
body: <p>I have users with datareader permission on my sql, the problem is that i noticed that the users are doing "fishing" query a lot (they don't know what exactly should be querying to get what thy need).</p>
  
  <p>This is a production SQL and we started to have deadlock because of those users.
  Trying to don't break their process i applied "deny view definition to [user]" so they cannot see the list of tables, view, stored procedure, so no more "fishing"</p>
  
  <p>This was a great solution, it works perfect if you connect directly to the SQL Server, you can run queries (select) but you cannot see the list of tables.</p>
  
  <p>However many users are using those account to connect to our SQL using linked server, and when the run a query using the linked server they are getting error:</p>
  
  <pre><code>SELECT TOP 100 * 
  FROM [SERVERSQL].[DATABASE].dbo.[TABLE]
  </code></pre>
  
  <blockquote>
    <p>Msg 7314, Level 16, State 1, Line 1<br>
    The OLE DB provider "SQLNCLI11" for linked server "SERVERSQL" does not contain the table ""[DATABASE]"."dbo"."[TABLE]"". The table either does not exist or the current user does not have permissions on that table.</p>
  </blockquote>
  
  <p>Using SQL Server version 2014 (v12.0.2000.8) and 2016 (v13.0.5492.2).</p>
  
  <p>What is the correct way to hide tables, view, stored procedures, etc but allow select and make it work on linked server?</p>
  
  <p>Thanks</p>