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?

  • Which RDBMS? You must have put a DENY VIEW DEFINITION ON YourTable TO someuser to not be able to retrieve a row from INFORMATION_SCHEMA.TABLES. By default a GRANT SELECT by itself would allow you to view the table params there. – Thomas Stringer Mar 29 '12 at 20:47
  • Also, I hope you realize that if you are having @TableType as an optional parameter with a default of NULL and the parameter isn't supplied, then that query won't return anything no matter what database user context is being executed under. – Thomas Stringer Mar 29 '12 at 20:54
  • I've updated the question to state that it's MS SQL, and removed the optional param to avoid confusion. – Overflew Apr 1 '12 at 21:23

Just add WHERE clause and use HAS_PERM_BY_NAME:

from information_schema.Tables 
where (TABLE_TYPE = @TableType) -- Optional parameter 
   'OBJECT', 'SELECT') = 1
order by TABLE_NAME
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.