Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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