SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

Question

I currently have a query in SQL 2008 that can return me a list of tables for a given database, provided I have View Definition access to them.

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.Tables 
order by TABLE_NAME 

The way our database roles are managed, the existing select permissions are nicely configured across roles, and adding the new View Definitions is done as a single schema-viewing role.

Is there a way I can limit this to the tables the current user has select permission on?

Answer 1

Just add WHERE clause and use HAS_PERM_BY_NAME:

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
from information_schema.Tables 
where (TABLE_TYPE = @TableType) -- Optional parameter 
AND HAS_PERM_BY_NAME (
   QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME),
   'OBJECT', 'SELECT') = 1
order by TABLE_NAME