Sql-server – SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

information-schemasql server

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?

Best 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

Related Solutions

Sql-server – Users cannot view tables in non-default schema in SSMS

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.

SQL Server – Minimum Rights to View Column Default Values

This will allow the defaults to show up in the lower pane of the table designer:

GRANT VIEW DEFINITION ON dbo.tablename TO [username];

Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your user can also pull this information from the catalog views:

SELECT 
  [constraint] = d.name, 
  [column] = c.name, 
  [default] = d.definition 
FROM sys.default_constraints AS d
  INNER JOIN sys.tables AS t
  ON d.parent_object_id = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE s.name = N'dbo'
    AND t.name = N'tablename';

Is there a way to do something similar for whole tables of database? I mean, about the GRANT VIEW DEFINITION ON dbo.tablename TO [username];.

Sure, dynamic SQL is very handy here.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N''GRANT VIEW DEFINITION ON ' 
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
  + N' TO [username];' + CHAR(13) + CHAR(10)
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Best Answer

Related Solutions

Sql-server – Users cannot view tables in non-default schema in SSMS

SQL Server – Minimum Rights to View Column Default Values

Related Question